Java数据库操作大全



Java数据库操作大全

http://blog.sina.com.cn/s/blog_531101430100liel.html

http://blog.csdn.net/gxiangzi/article/details/5504519

http://blog.csdn.net/laner0515/article/details/11266673

http://www.cnblogs.com/sirzxj/archive/2011/05/22/2174091.html

1.提取单条记录

 

  1. //import java.sql.*;
  2. Connection con=null;
  3. Statement stmt=null;
  4. ResultSet %%6=null;
  5. try {
  6. Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
  7. String url=”jdbc:odbc:”+%%1;
  8. con=DriverManager.getConnection(url,%%2,%%3);
  9. stmt=con.createStatement();
  10. stmt.executeUpdate(%%4);
  11. %%6=stmt.executeQuery(%%5);
  12. %%7
  13. }catch(Exception e){
  14. e.printStackTrace();
  15. }finally{
  16. try {
  17. try {
  18. stmt.close();
  19. con.close();
  20. } catch (SQLException e) {
  21. e.printStackTrace();
  22. }
  23. } catch (Exception e) {
  24. e.printStackTrace();
  25. }
  26. }

2.单值比较

 

 

  1. //import java.sql.*;
  2. ResultSet rs;
  3. String strurl=”jdbc:odbc:%%1″;
  4. Connection con=NULL;
  5. Statement stmt=null;
  6. try {
  7. Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
  8. con=DriverManager.getConnection(url,%%2,%%3);
  9. stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
  10. rs=stmt.executeQuery(%%2);
  11. rs.next();
  12. int %%3=rs.getInt(“%%3″);
  13. if(%%3==%%4)
  14. {
  15. %%5
  16. }
  17. }
  18. catch (Exception e) {
  19. e.printStackTrace();
  20. }
  21. finally{
  22. try {
  23. if(rs!=null)
  24. {
  25. rs.close();
  26. stmt.close();
  27. con.close();
  28. } catch (Exception e) {
  29. e.printStackTrace();
  30. }
  31. }

3.显示表格

 

 

  1. //import java.awt.*;
  2. //import javax.swing.*;
  3. //import java.sql.*;
  4. //import javax.swing.table.*;
  5. String[] colHeads=%%4;
  6. Connection con=null;
  7. Statement stmt=null;
  8. ResultSet %%6=null;
  9. try {
  10. Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
  11. String url=”jdbc:odbc:”+%%1;
  12. con=DriverManager.getConnection(url,%%2,%%3);
  13. stmt=con.createStatement();
  14. %%6=stmt.executeQuery(“SELECT count(*) as au_count from ”+%%5);
  15. %%6.next();
  16. int iCount=rs.getInt(“au_count”);
  17. Object[][] data=new Object[iCount][];
  18. int i=0;
  19. %%6=stmt.executeQuery(“SELECT * from ”+%%5);
  20. while(%%6.next()){
  21. data[i]=new Object[iCount];
  22. data[i][0]=%%6.getString(“au_fname”);
  23. data[i][1]=%%6.getString(“Phone”);
  24. data[i][2]=%%6.getString(“City”);
  25. i++;
  26. }
  27. JTable table=new JTable(data,colHeads);
  28. JScrollPane jsp=new JScrollPane(table);
  29. getContentPane().add(jsp);
  30. }catch(Exception e){
  31. e.printStackTrace();
  32. }
  33. finally{
  34. if(con!=null){
  35. try {
  36. %%6.close();
  37. stmt.close();
  38. con.close();
  39. } catch (SQLException e) {
  40. e.printStackTrace();
  41. }
  42. }
  43. }

4.操作表格

 

 

  1. //import java.awt.*;
  2. //import java.swing.*;
  3. //import java.sql.*;
  4. //import javax.swing.table.*;
  5. JTable table;
  6. Object[][] data;
  7. JButton jb;
  8. JScrollPane jsp;
  9. JComboBox jcb;
  10. JCheckBox jchb;
  11. jcb=new JComboBox();
  12. jchb=new JCheckBox();
  13. jcb.addItem(“男”);
  14. jcb.addItem(“女”);
  15. jb = new JButton(“dsfasd”);
  16. String[] colHeads = { ”id”, ”性别”,”是否” };
  17. Connection con = null;
  18. Statement stmt = null;
  19. ResultSet rs = null;
  20. try {
  21. Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
  22. String url = ”jdbc:odbc:ghost”;
  23. con = DriverManager.getConnection(url);
  24. stmt = con.createStatement();
  25. rs = stmt.executeQuery(“SELECT count(id) as count from AUTHORS”);
  26. rs.next();
  27. int iCount = rs.getInt(“count”);
  28. data = new Object[iCount][];
  29. int i = 0;
  30. rs = stmt.executeQuery(“SELECT * from AUTHORS”);
  31. while (rs.next()) {
  32. data[i] = new Object[iCount];
  33. data[i][0] = rs.getString(“id”);
  34. data[i][1] = rs.getString(“gender”);
  35. data[i][2] = rs.getString(“isMember”);
  36. i++;
  37. }
  38. table = new JTable();
  39. DefaultTableModel dt = new DefaultTableModel(data, colHeads); //
  40. table.setModel(dt);
  41. jsp = new JScrollPane(table);
  42. getContentPane().add(jsp, BorderLayout.CENTER);
  43. TableColumn tc = table.getColumn(“性别”);
  44. TableColumn tc2=table.getColumn(“是否”);
  45. tc.setCellEditor(new DefaultCellEditor(jcb));
  46. tc2.setCellEditor(new DefaultCellEditor(jchb));
  47. } catch (Exception e) {
  48. e.printStackTrace();
  49. } finally {
  50. if (con != null) {
  51. try {
  52. rs.close();
  53. stmt.close();
  54. } catch (SQLException e) {
  55. e.printStackTrace();
  56. }
  57. }
  58. }
  59. this.setSize(300, 300);
  60. this.setLocation(200, 210);
  61. JPanel jp2 = new JPanel();
  62. this.getContentPane().add(jp2, BorderLayout.NORTH);
  63. JPanel jp = new JPanel();
  64. jp2.add(jp);
  65. jp.add(new JTextField(10));
  66. this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
  67. this.setVisible(true);

 

5.数值范围查询

 

  1. //import java.sql.*;
  2. Connection con;
  3. Statement stmt;
  4. ResultSet rs;
  5. int min = Integer.parseInt(jcb1.getSelectedItem().toString());
  6. int max = Integer.parseInt(jcb2.getSelectedItem().toString());
  7. String sql = ”select count(*) as pro_count from ProPrice where price between ”
  8. + min + ” and ” + max;
  9. try {
  10. con = DriverManager.getConnection(url);
  11. stmt = con.createStatement();
  12. rs = stmt.executeQuery(sql);
  13. rs.next();
  14. int iCount = rs.getInt(“pro_count”);
  15. Object[][] data = new Object[iCount][];
  16. rs = stmt.executeQuery(“select * from ProPrice where price between ”
  17. + min + ” and ” + max);
  18. int i = 0;
  19. while (rs.next()) {
  20. data[i] = new Object[3];
  21. data[i][0] = rs.getString(“id”);
  22. data[i][1] = rs.getString(“name”);
  23. data[i][2] = rs.getString(“price”);
  24. i++;
  25. }
  26. DefaultTableModel dt = new DefaultTableModel(data, colHeads);
  27. table.setModel(dt);
  28. } catch (Exception e1) {
  29. e1.printStackTrace();
  30. } finally {
  31. try {
  32. rs.close();
  33. con.close();
  34. stmt.close();
  35. } catch (SQLException e1) {
  36. e1.printStackTrace();
  37. }
  38. }

6.关闭时关闭连接

 

 

  1. //import java.sql.*;
  2. addWindowListener(new WindowAdapter{
  3. public void windowClosing(WindowEvent wevent){
  4. if(stmt!=null){
  5. try {
  6. stmt.close();
  7. con.close();
  8. } catch (Exception e) {
  9. e.printStackTrace();
  10. }
  11. }
  12. }

7.执行命令

 

 

  1. //import java.sql.*;
  2. Connection conn;
  3. PreparedStatement pst=null;
  4. try {
  5. conn=DriverManager.getConnection(url);
  6. pst=conn.prepareStatement(“Insert Into grade(%%1) Values (?)”);
  7. pst.setInt(1,%%2);
  8. //pst.setInt(2,%%2);
  9. pst.addBatch();
  10. pst.executeBatch();
  11. } catch (SQLException e){
  12. e.printStackTrace();
  13. }
  14. finally{
  15. pst.close();
  16. conn.close();
  17. }
  19. 8.Oracle8/8i/9i数据库（thin模式）
  20. //import java.sql.*;
  21. Class.forName(“oracle.jdbc.driver.OracleDriver”).newInstance();
  22. String url=”jdbc:oracle:thin:@localhost:1521:orcl”; //orcl为数据库的SID
  23. String user=”test”;
  24. String password=”test”;
  25. Connection conn= DriverManager.getConnection(url,user,password);
  26. Statement stmtNew=conn.createStatement();

9.DB2数据库

 

 

  1. //import java.sql.*;
  2. Class.forName(“com.ibm.db2.jdbc.app.DB2Driver ”).newInstance();
  3. String url=”jdbc:db2://localhost:5000/sample”; //sample为你的数据库名
  4. String user=”admin”;
  5. String password=”";
  6. Connection conn= DriverManager.getConnection(url,user,password);
  7. Statement stmtNew=conn.createStatement();


10.Sql Server7.0/2000数据库

 

 

  1. //import java.sql.*;
  2. Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”).newInstance();
  3. //String url=”jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=db2″; //7.0、2000
  4. String url=”jdbc:sqlserver://localhost:1433;DatabaseName=db2″; //2005
  5. //db2为数据库名
  6. String user=”sa”;
  7. String password=”";
  8. Connection conn= DriverManager.getConnection(url,user,password);
  9. Statement stmtNew=conn.createStatement();

11.Sybase数据库

 

 

  1. //import java.sql.*;
  2. Class.forName(“com.sybase.jdbc.SybDriver”).newInstance();
  3. String url =” jdbc:sybase:Tds:localhost:5007/myDB”;//myDB为你的数据库名
  4. Properties sysProps = System.getProperties();
  5. SysProps.put(“user”,”userid”);
  6. SysProps.put(“password”,”user_password”);
  7. Connection conn= DriverManager.getConnection(url, SysProps);
  8. Statement stmtNew=conn.createStatement();

12.Informix数据库

 

 

  1. //import java.sql.*;
  2. Class.forName(“com.informix.jdbc.IfxDriver”).newInstance();
  3. String url = ”jdbc:informix-sqli://123.45.67.89:1533/myDB:INFORMIXSERVER=myserver;
  4. user=testuser;password=testpassword”; //myDB为数据库名
  5. Connection conn= DriverManager.getConnection(url);
  6. Statement stmtNew=conn.createStatement();

13.MySQL数据库

 

 

  1. //import java.sql.*;
  2. //Class.forName(“org.gjt.mm.mysql.Driver”).newInstance();
  3. Class.forName(“com.mysql.jdbc.Driver”);
  4. //String url =”jdbc:mysql://localhost/myDB?user=soft&password=soft1234&useUnicode=true&characterEncoding=8859_1″;
  5. String url =”jdbc:mysql://localhost:3306/myDB”;
  6. //myDB为数据库名
  7. Connection conn= DriverManager.getConnection(url,”root”,”root”);
  8. Statement stmtNew=conn.createStatement();

14.PostgreSQL数据库

 

 

  1. //import java.sql.*;
  2. Class.forName(“org.postgresql.Driver”).newInstance();
  3. String url =”jdbc:postgresql://localhost/myDB” //myDB为数据库名
  4. String user=”myuser”;
  5. String password=”mypassword”;
  6. Connection conn= DriverManager.getConnection(url,user,password);
  7. Statement stmtNew=conn.createStatement();

15.access数据库直连用ODBC的

 

 

  1. //import java.sql.*;
  2. Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”) ;
  3. String url=”jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=”+application.getRealPath(“/Data/ReportDemo.mdb”);
  4. Connection conn = DriverManager.getConnection(url,”sa”,”");
  5. Statement stmtNew=conn.createStatement();

16.程序计时

  1. long time1=System.currentTimeMillis();
  2. long time2=System.currentTimeMillis();
  3. long interval=time2-time1;

17.延时

 

 

  1. try {
  2. Thread.sleep(Integer.Parse(%%1));
  3. } catch(InterruptedException e) {
  4. e.printStackTrace();
  5. }

18.连接Excel文件

 

 

  1. //import java.sql.*;
  2. Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
  3. String url = ”jdbc:odbc:driver={Microsoft Excel Driver (*.xls)};DBQ=D:\\myDB.xls”; // 不设置数据源
  4. String user=”myuser”;
  5. String password=”mypassword”;
  6. Connection conn= DriverManager.getConnection(url,user,password);
  7. Statement stmtNew=conn.createStatement();