Java与数据库学习总结

2019-02-27 11:53:00来源：博客园阅读 ()

1、连接数据库

 1 package utils;
 2 
 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 
 9 public class JDBCUtils {
10     private JDBCUtils() {
11     }
12 
13     private static Connection con;
14     static {
15         try {
16             // 注册驱动
17             Class.forName("com.mysql.jdbc.Driver");
18             // 获得连接 对象
19             String url = "jdbc:mysql://localhost:3306/day35";
20             // 账号密码
21             String username = "root";
22             String password = "root";
23             // 连接数据库
24             con = DriverManager.getConnection(url, username, password);
25         } catch (Exception e) {
26             e.printStackTrace();
27         }
28     }
29 
30     // 定义静态方法 返回数据库的连接
31     public static Connection getConnection() {
32         return con;
33     }
34 
35     // 关资源
36     public static void close(Connection con, Statement stat) {
37         if (stat != null) {
38             try {
39                 stat.close();
40             } catch (SQLException e) {
41                 e.printStackTrace();
42             }
43             if (con != null) {
44                 try {
45                     con.close();
46                 } catch (SQLException e) {
47                     e.printStackTrace();
48                 }
49             }
50         }
51     }
52 
53     public static void close(Connection con, Statement stat, ResultSet rs) {
54         if (rs != null) {
55             try {
56                 rs.close();
57             } catch (SQLException e) {
58 
59                 e.printStackTrace();
60             }
61             if (stat != null) {
62                 try {
63                     stat.close();
64                 } catch (SQLException e) {
65                     e.printStackTrace();
66                 }
67                 if (con != null) {
68                     try {
69                         con.close();
70                     } catch (SQLException e) {
71                         e.printStackTrace();
72                     }
73                 }
74             }
75         }
76     }
77 }

还可以通过配置文件方式连接数据库

配置文件(database.properties)：

1 driverClass=com.mysql.jdbc.Driver
2 url=jdbc:mysql://localhost:3306/day35
3 username=root
4 password=root

Java：

 1 package utils;
 2 
 3 import java.io.InputStream;
 4 import java.sql.Connection;
 5 import java.sql.DriverManager;
 6 import java.util.Properties;
 7 
 8 public class JDBCUtilsConfig {
 9     private static Connection con;
10     private static String driverClass;
11     private static String url;
12     private static String username;
13     private static String password;
14     static {
15         try {
16             readConfig();
17             Class.forName(driverClass);
18             con = DriverManager.getConnection(url, username, password);
19         } catch (Exception e) {
20             e.printStackTrace();
21         }
22     }
23 
24     // 读配置文件
25     private static void readConfig() throws Exception {
26         // 类加载器
27         InputStream in = JDBCUtilsConfig.class.getClassLoader().getResourceAsStream("database.properties");
28         Properties p = new Properties();
29         p.load(in);
30         driverClass = p.getProperty("driverClass");
31         url = p.getProperty("url");
32         username = p.getProperty("username");
33         password = p.getProperty("password");
34     }
35 
36     public static Connection getConnection() {
37         return con;
38     }
39 }

2、访问数据库

 1 import java.sql.Connection;
 2 import java.sql.DriverManager;
 3 import java.sql.SQLException;
 4 import java.sql.Statement;
 5 
 6 public class JDBCDemo {   
 7     public static void main(String[] args) throws ClassNotFoundException, SQLException {
 8         Class.forName("com.mysql.jdbc.Driver");
 9         String url = "jdbc:mysql://localhost:3306/day35";
10         String uname = "root";
11         String pwd = "root";
12         Connection con = DriverManager.getConnection(url, uname, pwd);
13         //获得语句执行平台
14         Statement sta = con.createStatement();
15         sta.executeUpdate("insert into money (name) values ('we232')");
16         sta.close();
17         con.close();
18     }
19 }

 1 import java.sql.Connection;
 2 import java.sql.DriverManager;
 3 import java.sql.ResultSet;
 4 import java.sql.SQLException;
 5 import java.sql.Statement;
 6 
 7 //  查询
 8 public class JDBCDemo1 {
 9 
10     public static void main(String[] args) throws ClassNotFoundException, SQLException {
11         // 1 注册驱动
12         Class.forName("com.mysql.jdbc.Driver");
13         // 2获得连接 对象
14         String url = "jdbc:mysql://localhost:3306/day35";
15         String username = "root";
16         String password = "root";
17         Connection con = DriverManager.getConnection(url, username, password);
18         // 3 获取执行sql语句对象
19         Statement stat = con.createStatement();// 有注入攻击风险，不建议使用，可以用另一种平台
20         // 4 调用执行者对象的方法
21         String sql = "SELECT * FROM sort";
22         ResultSet rs = stat.executeQuery(sql);
23         // 5 rs结果集 ResultSet 方法 bollean next(); 返回true 有结果返回false 没有
24         while (rs.next()) {
25             // 获取每一列数据 ResultSet getxxx方法
26             System.out.println(rs.getInt("sid") + "  " + rs.getDouble("sprice") + "   " + rs.getString("sdesc"));
27         }
28         // 5 关资源
29         rs.close();
30         stat.close();
31         con.close();
32     }
33 }

另一种语句执行平台

 1 import java.sql.Connection;
 2 import java.sql.DriverManager;
 3 import java.sql.PreparedStatement;
 4 import java.sql.SQLException;
 5 
 6 public class JDBCDemo2 {
 7     public static void main(String[] args) throws ClassNotFoundException, SQLException {
 8         // 1 注册驱动
 9         Class.forName("com.mysql.jdbc.Driver");
10         // 2获得连接 对象
11         String url = "jdbc:mysql://localhost:3306/day35";
12         String username = "root";
13         String password = "root";
14         Connection con = DriverManager.getConnection(url, username, password);
15         // 3 获得执行对象 换执行平台对象 prepareStatement
16         String sql = "UPDATE sort SET sname=?,sprice=? WHERE sid=?";
17         PreparedStatement pst = con.prepareStatement(sql);
18         // pst 有方法 setxxx(占位符的位置，值)
19         pst.setObject(1, "饭缸");
20         pst.setObject(2, "20000");
21         pst.setObject(3, 1);
22         int s = pst.executeUpdate();
23         System.out.println(s);
24         // 4 关
25         pst.close();
26         con.close();
27     }
28 }

3、使用第三方工具包访问数据库

 1 import java.sql.Connection;
 2 import java.sql.SQLException;
 3 
 4 import org.apache.commons.dbutils.DbUtils;
 5 import org.apache.commons.dbutils.QueryRunner;
 6 
 7 public class Demo2 {
 8     private static Connection con = JDBCUtilsConfig.getConnection();
 9 
10     public static void main(String[] args) throws SQLException {
11         insert();
12         update();
13         delete();
14     }
15 
16     public static void insert() throws SQLException {
17         QueryRunner qr = new QueryRunner();
18         String sql = "insert into sort (sname,sprice,sdesc) values (?,?,?)";
19         Object[] param = { "猫", 555, "小橘猫" };
20         qr.update(con, sql, param);
21         DbUtils.close(con);
22     }
23 
24     public static void update() throws SQLException {
25         QueryRunner qr = new QueryRunner();
26         String sql = "update sort set sname = ?,sprice = ?,sdesc = ? where id = ?";
27         Object[] param = { "qy95", 40, "AAA", 6 };
28         qr.update(con, sql, param);
29         DbUtils.close(con);
30     }
31 
32     public static void delete() throws SQLException {
33         QueryRunner qr = new QueryRunner();
34         String sql = "delete from sort where id = ?";
35         qr.update(con, sql, 9);
36         DbUtils.close(con);
37     }
38 }

4、第三方包8种结果集处理方式

  1 package cn.zlh.Demo;
  2 
  3 import java.sql.Connection;
  4 import java.sql.SQLException;
  5 import java.util.List;
  6 import java.util.Map;
  7 
  8 import javax.management.Query;
  9 
 10 import org.apache.commons.dbutils.DbUtils;
 11 import org.apache.commons.dbutils.QueryRunner;
 12 import org.apache.commons.dbutils.handlers.ArrayHandler;
 13 import org.apache.commons.dbutils.handlers.ArrayListHandler;
 14 import org.apache.commons.dbutils.handlers.BeanHandler;
 15 import org.apache.commons.dbutils.handlers.BeanListHandler;
 16 import org.apache.commons.dbutils.handlers.ColumnListHandler;
 17 import org.apache.commons.dbutils.handlers.MapHandler;
 18 import org.apache.commons.dbutils.handlers.MapListHandler;
 19 import org.apache.commons.dbutils.handlers.ScalarHandler;
 20 
 21 import cn.zlh.domain.Sort;
 22 import cn.zlh.utils.JDBCUtilsConfig;
 23 
 24 // 8种结果集处理
 25 public class Demo3 {
 26     private static Connection con = JDBCUtilsConfig.getConnection();
 27 
 28     public static void main(String[] args) throws SQLException {
 29         arrayHandler();
 30         arrayListHandler();
 31         beanHandler();
 32         beanListHandler();
 33         columnListHandler();
 34         scalarHandler();
 35         mapHandler();
 36         mapListHandler();
 37     }
 38 
 39     // 将每一条数据都以key-value的形式放在Map集合中，再把这些Map放到一个List集合中
 40     public static void mapListHandler() throws SQLException {
 41         QueryRunner qr = new QueryRunner();
 42         String sql = "select * from sort";
 43         List<Map<String, Object>> query = qr.query(con, sql, new MapListHandler());
 44         DbUtils.close(con);
 45         for (Map<String, Object> map : query) {
 46             for (String key : map.keySet()) {
 47                 System.out.print(key + ":" + map.get(key));
 48             }
 49             System.out.println();
 50         }
 51     }
 52 
 53     // 将第一天数据以key-value的形式放在Map集合中
 54     public static void mapHandler() throws SQLException {
 55         QueryRunner qr = new QueryRunner();
 56         String sql = "select * from sort";
 57         Map<String, Object> query = qr.query(con, sql, new MapHandler());
 58         DbUtils.close(con);
 59         for (String key : query.keySet()) {
 60             System.out.println(key + ":" + query.get(key));
 61         }
 62     }
 63 
 64     // 可以使用聚合函数统计数据
 65     public static void scalarHandler() throws SQLException {
 66         QueryRunner qr = new QueryRunner();
 67         String sql = "select count(*) from sort";
 68         Long query = qr.query(con, sql, new ScalarHandler<Long>());
 69         DbUtils.close(con);
 70         System.out.println(query);
 71     }
 72 
 73     // 将所有数据中某一列的值放到一个List集合中
 74     public static void columnListHandler() throws SQLException {
 75         QueryRunner qr = new QueryRunner();
 76         String sql = "select * from sort";
 77         List<Object> query = qr.query(con, sql, new ColumnListHandler<Object>("sprice"));
 78         DbUtils.close(con);
 79         for (Object o : query) {
 80             System.out.println(o);
 81         }
 82     }
 83 
 84     // 将每一条数据各自封装成一个对象，再把这些对象放到一个List集合里
 85     public static void beanListHandler() throws SQLException {
 86         QueryRunner qr = new QueryRunner();
 87         String sql = "select * from sort";
 88         List<Sort> query = qr.query(con, sql, new BeanListHandler<Sort>(Sort.class));
 89         DbUtils.close(con);
 90         for (Sort s : query) {
 91             System.out.println(s);
 92         }
 93     }
 94 
 95     // 将第一条数据封装成一个对象
 96     public static void beanHandler() throws SQLException {
 97         QueryRunner qr = new QueryRunner();
 98         String sql = "select * from sort";
 99         Sort query = qr.query(con, sql, new BeanHandler<>(Sort.class));
100         DbUtils.close(con);
101         System.out.println(query);
102     }
103 
104     // 将每一条数据各自放到一个数组里，再把这些数组放到一个List集合里
105     public static void arrayListHandler() throws SQLException {
106         QueryRunner qr = new QueryRunner();
107         String sql = "select * from sort";
108         List<Object[]> query = qr.query(con, sql, new ArrayListHandler());
109         DbUtils.close(con);
110         for (Object[] o : query) {
111             for (Object obj : o) {
112                 System.out.print(obj + ",");
113             }
114             System.out.println();
115         }
116     }
117 
118     // 将第一条数据放到数组里
119     public static void arrayHandler() throws SQLException {
120         QueryRunner qr = new QueryRunner();
121         String sql = "select * from sort";
122         Object[] query = qr.query(con, sql, new ArrayHandler());
123         DbUtils.close(con);
124         for (Object o : query) {
125             System.out.println(o);
126         }
127     }
128 }