1.JDBC基本使用
导读补充
1.1.JDBC是什么？
java DataBase Connectivity (Java语言连接数据库)
1.2.JDBC的本质是什么？
JDBC是sun公司制定的一套接口（interface）
接口都有调用者和实现者。
面向接口调用、面向接口写实现类，这都属于面向接口编程。
- 为什么要面向接口编程？
- 解耦合：降低程序的耦合度，提高程序的扩展力。
- 多态机制就是非常典型的：面向抽象编程。(不要面向具体编程)
1.3.JDBC七步走
package com.Li.JDBCs; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * @Description: JDBC七步走 * @auther:Li Ya Hui * @Time:2021年5月20日下午3:41:38 */ public class Test { public static void main(String[] args) throws ClassNotFoundException, SQLException{ //连库四要素 String driverName = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/class2021change?useUnicode=true&characterEncoding=utf-8"; String userName = "root"; String Password = "root"; String sql_query = "select * from student"; //1.加载驱动 Class.forName(driverName); //2.指定URL/用户名+密码 Connection conn = DriverManager.getConnection(url, userName, Password); //3.获取链接 if(!conn.isClosed()) { System.out.println("连接成功了"); //4.创建stmt对象 Statement stmt = conn.createStatement(); //5.执行sql语句 ResultSet rs = stmt.executeQuery(sql_query); //6.循环结果集对象 while (rs.next()) { System.out.println(rs.getString("sno")+"\t"+rs.getString("sname")+"\t"+rs.getInt("sage")+"\t"+rs.getString("ssex")); } //7.关闭连接 rs.close(); stmt.close(); conn.close(); } } }
2.Dao
把jdbc的七步走 以及增删改查操作封装成自己的类
package com.Li.dao; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @Description: 利用JDBC重构获取一个Dao工具 * @auther:Li Ya Hui * @Time:2021年5月20日下午6:40:12 */ public class Dao { //连库四要素 private String driverName = "com.mysql.jdbc.Driver"; private String url = "jdbc:mysql://localhost:3306/class2021change?useUnicode=true&characterEncoding=utf-8"; private String userName = "root"; private String Password = "root"; /** * @return 1.获取链接 * @throws ClassNotFoundException * @throws SQLException */ private Connection getConnection() throws ClassNotFoundException, SQLException { //1.加载驱动 Class.forName(driverName); //2.指定URL/用户名+密码 Connection conn = DriverManager.getConnection(url, userName, Password); return conn; } /** * @desc 2.释放连接 * @param conn * @param stmt * @param rs * @throws SQLException */ private void releaseConnection(Connection conn, Statement stmt, ResultSet rs) throws SQLException { //7.关闭连接 if (rs!=null) { rs.close(); } if (stmt!=null) { stmt.close(); } if(conn!=null&!conn.isClosed()) { conn.close(); } System.out.println("releaseConnection"); } //ps:我们对数据库的操作，可以最终归纳为四个动作：增删改查 /** * @desc 3.查询全部 * @param sql * @return * @throws ClassNotFoundException * @throws SQLException */ public List<Map< String , Object >> executeQueryForList(String sql) throws ClassNotFoundException, SQLException{ System.out.println("查询全部sql语句"+sql); Connection conn = this.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); List<Map<String ,Object>> list = rsToList(rs); this.releaseConnection(conn, stmt, rs); return list; } /** * //5.查询单条记录 * @param sql * @return * @throws ClassNotFoundException * @throws SQLException */ public Map<String, Object> executeQueryForMap(String sql) throws ClassNotFoundException, SQLException{ System.out.println("查询一条信息"); Connection conn = null ; Statement stmt = null; ResultSet rs = null; List<Map<String, Object>> list; try { conn = this.getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); list = this.rsToList(rs); if(!list.isEmpty()) { return list.get(0); } } //释放资源 finally { this.releaseConnection(conn, stmt, rs); } return null; } /** * @desc 6.查询一共有多少条 * @param sql * @return * @throws SQLException * @throws ClassNotFoundException */ public int executeQueryForCount(String sql) throws SQLException, ClassNotFoundException { System.out.println("查询一共有多少条的sql:"+sql); Connection conn = null; Statement stmt = null; ResultSet rs = null; try { conn = this.getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if(rs.next()) { System.out.println("ss"); return rs.getInt(1); } }finally { releaseConnection(conn, stmt, rs); } return 0; } /** * @desc 7.执行添加、删除、修改操作 * @param sql * @return * @throws ClassNotFoundException * @throws SQLException */ public int executeUpdate(String sql) throws ClassNotFoundException, SQLException { System.out.println("执行添加、修改、删除、等操作的sql"+sql); Connection conn = this.getConnection(); Statement stmt = conn.createStatement(); //执行sql语句 int count = stmt.executeUpdate(sql); this.releaseConnection(conn, stmt); return 0; } /** * @desc 关闭连接 * @param conn * @param stmt * @throws SQLException */ private void releaseConnection(Connection conn, Statement stmt) throws SQLException { if (stmt!=null) { stmt.close(); } if(conn!=null&!conn.isClosed()) { conn.close(); } System.out.println("releaseConnection"); } /** * 4.将rs结果集转变为List * @param rs * @return * @throws SQLException */ private List<Map<String, Object>> rsToList(ResultSet rs) throws SQLException { List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>(); System.out.println("表中有："+rs.getMetaData().getColumnCount()+"列"); while (rs.next()) //控制循环行 { //创建一个map 收集数据 Map<String, Object> colsMap = new HashMap<String, Object>(); //根据 for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) { switch (rs.getMetaData().getColumnType(i)) { case Types.VARCHAR: colsMap.put(rs.getMetaData().getColumnName(i), rs.getString(i)); break; case Types.INTEGER: colsMap.put(rs.getMetaData().getColumnName(i), rs.getInt(i)); break; case Types.BLOB://二进制 图片类型 InputStream in = rs.getBinaryStream(i); colsMap.put(rs.getMetaData().getColumnName(i), in); break; default: colsMap.put(rs.getMetaData().getColumnName(i), rs.getString(i)); break; } } rows.add(colsMap); } return rows; } public static void main(String[] args) throws Exception{ Dao dao = new Dao(); //执行查询表 System.out.println(dao.executeQueryForList("select * from student")); //执行单条记录 System.out.println(dao.executeQueryForMap("select * from student where sno='s001' ")); //执行查询条数 System.out.println(dao.executeQueryForCount("select count(*) from student ")); //执行修改 System.out.println(dao.executeUpdate("update student set sname='李亚辉' where sname='张三'")); } }
