我要评论 1 import java.io.ioexception;
2 import java.io.inputstream;
3 import java.sql.connection;
4 import java.sql.drivermanager;
5 import java.sql.resultset;
6 import java.sql.sqlexception;
7 import java.sql.statement;
8 import java.util.properties;
9
10 import org.apache.commons.dbcp.basicdatasource;
11
12 public class dbutils {
13 private static basicdatasource datesource;
14 static {
15 //创建属性对象
16 properties prop = new properties();
17 //得到文件的输入流
18 inputstream ips = dbutils2.class.getclassloader().getresourceasstream("jdbc.properties");
19 //把文件加载到属性对象中
20 try {
21 prop.load(ips);
22 //读取数据
23 string driver = prop.getproperty("driver");
24 string url = prop.getproperty("url");
25 string username = prop.getproperty("username");
26 string password = prop.getproperty("password");
27 //创建数据源对象
28 datesource = new basicdatasource();
29 //设置数据库链接信息
30 datesource.setdriverclassname(driver);
31 datesource.seturl(url);
32 datesource.setusername(username);
33 datesource.setpassword(password);
34 //设置连接池参数
35 datesource.setinitialsize(3);//初始连接数量
36 datesource.setmaxactive(5);//最大连接数量
37
38 } catch (ioexception e) {
39 e.printstacktrace();
40 }
41
42 }
43 //1、获取链接
44 public static connection getconn() throws exception {
45
46
47 //获取连接池中的连接
48 connection conn = datesource.getconnection();
49 return conn;
50 }
51 //2、关闭资源
52 public static void close(resultset rs,statement stat,connection conn) {
53 try {
54 if (rs!=null) {
55 rs.close();
56 }
57 } catch (sqlexception e) {
58 e.printstacktrace();
59 }
60 try {
61 if (stat!=null) {
62 stat.close();
63 }
64 } catch (sqlexception e) {
65 e.printstacktrace();
66 }
67 //关闭连接
68 try {
69 if (conn!=null) {
70 conn.close();
71 }
72 } catch (sqlexception e) {
73 e.printstacktrace();
74 }
75 }
76 }
dao
1 /**
2 * 依据用户名查询对应的用户信息。 如果找不到，返回null。
3 *
4 * @throws sqlexception
5 */
6 public user find(string uname) throws sqlexception {
7 user user = null;
8
9 connection conn = null;
10 preparedstatement ps = null;
11 resultset rs = null;
12
13 try {
14 conn = dbutils.getconn();
15 string sql = "select * from t_user " + "where username=?";
16 ps = conn.preparestatement(sql);
17 ps.setstring(1, uname);
18 rs = ps.executequery();
19
20 if (rs.next()) {
21 int id = rs.getint("id");
22 string pwd = rs.getstring("password");
23 string email = rs.getstring("email");
24
25 user = new user();
26 user.setid(id);
27 user.setuname(uname);
28 user.setpwd(pwd);
29 user.setemail(email);
30
31 }
32
33 } catch (sqlexception e) {
34 e.printstacktrace();
35 throw e;
36 } finally {
37 dbutils.close(rs, ps, conn);
38 }
39
40 return user;
41 }
42 /**
43 * 删除指定信息
44 * @param id
45 * @throws sqlexception
46 */
47 public void delete(int id) throws sqlexception {
48 connection conn = null;
49 preparedstatement ps = null;
50
51 try {
52 conn = dbutils.getconn();
53 string sql = "delete from t_user " + "where id = ?";
54 ps = conn.preparestatement(sql);
55 ps.setint(1, id);
56 ps.executeupdate();
57
58 } catch (sqlexception e) {
59 e.printstacktrace();
60 throw e;
61 } finally {
62 dbutils.close(null, ps, conn);
63 }
64 }
65
66 /**
67 * 将用户信息插入到t_user表。
68 *
69 * @throws sqlexception
70 *
71 */
72 public void save(user user) throws sqlexception {
73 connection conn = null;
74 preparedstatement ps = null;
75
76 try {
77 conn = dbutils.getconn();
78 string sql = "insert into t_user " + "values(null,?,?,?)";
79 ps = conn.preparestatement(sql);
80 ps.setstring(1, user.getuname());
81 ps.setstring(2, user.getpwd());
82 ps.setstring(3, user.getemail());
83 ps.executeupdate();
84
85 } catch (sqlexception e) {
86 e.printstacktrace();
87 throw e;
88 } finally {
89 dbutils.close(null, ps, conn);
90 }
91 }
92
93 /**
94 * 从t_user表中查询出所有用户的信息。 注： 一条记录对应一个user对象(即将记录中的数据 存放到user对象里面)。
95 *
96 * @throws sqlexception
97 */
98 public list<user> findall() throws sqlexception {
99
100 list<user> users = new arraylist<user>();
101
102 connection conn = null;
103 preparedstatement ps = null;
104 resultset rs = null;
105
106 try {
107 conn = dbutils.getconn();
108 string sql = "select * from t_user";
109 ps = conn.preparestatement(sql);
110 rs = ps.executequery();
111
112 while (rs.next()) {
113
114 int id = rs.getint("id");
115 string uname = rs.getstring("username");
116 string pwd = rs.getstring("password");
117 string email = rs.getstring("email");
118
119 user user = new user();
120 user.setid(id);
121 user.setuname(uname);
122 user.setpwd(pwd);
123 user.setemail(email);
124
125 users.add(user);
126
127 }
128
129 } catch (sqlexception e) {
130 e.printstacktrace();
131 throw e;
132 } finally {
133 dbutils.close(rs, ps, conn);
134 }
135
136 return users;
137
138 }
java中访问数据库的步骤
1 //注册驱动
2 class.forname("com.mysql.jdbc.driver");
3 //建立连接
4 connection conn = drivermanager.getconnection("jdbc:mysql://localhost:3306/db3", "root", "");
5 system.out.println("创建完毕");
6 //创建statement
7 statement stat = conn.createstatement();
8 string sql = "delete from jdbc01 where id=1";
9 //执行sql语句(若sql语句为查询语句需要处理结果集)
10 stat.executeupdate(sql);
11 system.out.println("删除完毕");
12 //关闭连接
13 stat.close();
14 conn.close();
数据库的基本连接
1 public static void main(string[] args) throws exception {
2 //创建数据源对象
3 basicdatasource datesource = new basicdatasource();
4 //设置数据库连接信息
5 datesource.setdriverclassname("com.mysql.jdbc.driver");
6 datesource.seturl("jdbc:mysql://localhost:3306/db3");
7 datesource.setusername("root");
8 datesource.setpassword("root");
9 //设置连接池参数
10 datesource.setinitialsize(3);//初始连接数量
11 datesource.setmaxactive(5);//最大连接数量
12 //获取连接池中的连接
13 connection conn = datesource.getconnection();
14 system.out.println(conn);
15 }
如对本文有疑问， 点击进行留言回复！！
MySQL-关系代数-并、交、差、等值连接、自然连接、左连接。。。
网友评论