SQL
2017-08-14 17:48 lc_java 阅读(235) 评论(0) 编辑 收藏 举报1 package com.JDBCsql; 2 3 import java.io.FileInputStream; 4 import java.io.FileNotFoundException; 5 import java.io.IOException; 6 import java.sql.Connection; 7 import java.sql.DriverManager; 8 import java.sql.PreparedStatement; 9 import java.sql.ResultSet; 10 import java.sql.SQLException; 11 import java.util.Properties; 12 13 public class TestMysql { 14 private static String DRIVER=null; 15 private static String URL=null; 16 private static String USERNAME = null; 17 private static String PASSWORD = null; 18 static{ 19 try { 20 FileInputStream fis 21 = new FileInputStream("src/db.properties"); 22 Properties pt = new Properties(); 23 pt.load(fis); 24 DRIVER = pt.getProperty("DRIVER"); 25 URL = pt.getProperty("URL"); 26 USERNAME = pt.getProperty("USERNAME"); 27 PASSWORD = pt.getProperty("PASSWORD"); 28 } catch (FileNotFoundException e) { 29 // TODO Auto-generated catch block 30 e.printStackTrace(); 31 } catch (IOException e) { 32 // TODO Auto-generated catch block 33 e.printStackTrace(); 34 } 35 } 36 public static Connection getConn(){ 37 Connection conn=null; 38 try { 39 /*��������*/ 40 // System.out.println(DRIVER); 41 Class.forName(DRIVER); 42 /*�������ݿ�*/ 43 conn= DriverManager.getConnection(URL, USERNAME, PASSWORD); 44 45 } catch (ClassNotFoundException e) { 46 // TODO Auto-generated catch block 47 e.printStackTrace(); 48 } catch (SQLException e) { 49 // TODO Auto-generated catch block 50 e.printStackTrace(); 51 } 52 return conn; 53 } 54 //�ر����� 55 public static void close(Connection conn){ 56 try { 57 conn.close(); 58 } catch (SQLException e) { 59 // TODO Auto-generated catch block 60 e.printStackTrace(); 61 } 62 63 } 64 //�ر����� 65 public static void close(PreparedStatement ps){ 66 try { 67 ps.close(); 68 } catch (SQLException e) { 69 // TODO Auto-generated catch block 70 e.printStackTrace(); 71 } 72 73 } 74 //�ر����� 75 public static void close(ResultSet rs){ 76 try { 77 rs.close(); 78 } catch (SQLException e) { 79 // TODO Auto-generated catch block 80 e.printStackTrace(); 81 } 82 83 } 84 85 86 public static int executeUpdate(String sql,Object... papram) { 87 // TODO Auto-generated method stub 88 Connection conn =getConn(); 89 int result=0; 90 try { 91 PreparedStatement pst = conn.prepareStatement(sql); 92 if (papram!=null) { 93 for (int i = 0; i < papram.length; i++) { 94 pst.setObject(i+1, papram[i]); 95 } 96 } 97 result =pst.executeUpdate(); 98 } catch (SQLException e) { 99 // TODO Auto-generated catch block 100 e.printStackTrace(); 101 }finally { 102 close(conn); 103 } 104 return result; 105 106 } 107 108 }
public static int delete(int id){ return TestMysql.executeUpdate("delete from user where id=?",id); } public static int update(TestUser testUser){ return TestMysql.executeUpdate("update user set name=?,pasword=?,mail=?,root=? where id=?",testUser.getName(),testUser.getPasword(),testUser.getMail(),testUser.getRoot(),testUser.getId()); } public static int update1(TestUser testUser){ return TestMysql.executeUpdate("update user set name=?,pasword=?,mail=?where id=?",testUser.getName(),testUser.getPasword(),testUser.getMail(),testUser.getId()); } public static int add(TestUser testUser){ return TestMysql.executeUpdate("insert into user (name,pasword,mail,root) values (?,?,?,?)",testUser.getName(),testUser.getPasword(),testUser.getMail(),testUser.getRoot()); } public static TestUser getUser(TestUser testUser){ Connection conn = TestMysql.getConn(); TestUser testUser4 =new TestUser(); ResultSet index ;//��¼���� PreparedStatement pst = null; String sql = "select * from user where name='"+testUser.getName()+"' and pasword='"+testUser.getPasword()+"'"; try { pst = conn.prepareStatement(sql); index = pst.executeQuery(); if (index.next()==false) { return new TestUser(); } else { testUser4.setId(index.getInt("id")); testUser4.setName(index.getString("name")); testUser4.setPasword(index.getInt("pasword")); testUser4.setMail(index.getString("mail")); testUser4.setRoot(index.getString("root")); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { try { pst.close(); conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } return testUser4; }