Java与数据库学习总结
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 }
