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;
}