JDBC示例程序

 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     }

 

 

posted @ 2018-10-24 10:07  國仕無雙  阅读(350)  评论(0编辑  收藏  举报