欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

JDBC示例程序

程序员文章站 2022-03-30 18:01:39
DAO Java中访问数据库的步骤 数据库的基本连接 ......
 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     }