【JDBC】JDBC学习（一）
1、引入依赖
1 <?xml version="1.0" encoding="UTF-8"?> 2 3 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 4 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> 5 <modelVersion>4.0.0</modelVersion> 6 7 <groupId>com.sheep</groupId> 8 <artifactId>jdbc-study</artifactId> 9 <version>1.0-SNAPSHOT</version> 10 11 <name>jdbc-study</name> 12 <!-- FIXME change it to the project's website --> 13 <url>http://www.example.com</url> 14 15 <properties> 16 <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> 17 <maven.compiler.source>1.8</maven.compiler.source> 18 <maven.compiler.target>1.8</maven.compiler.target> 19 </properties> 20 21 <dependencies> 22 <dependency> 23 <groupId>junit</groupId> 24 <artifactId>junit</artifactId> 25 <version>4.11</version> 26 <scope>test</scope> 27 </dependency> 28 29 <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> 30 <dependency> 31 <groupId>mysql</groupId> 32 <artifactId>mysql-connector-java</artifactId> 33 <version>8.0.22</version> 34 </dependency> 35 36 <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok --> 37 <dependency> 38 <groupId>org.projectlombok</groupId> 39 <artifactId>lombok</artifactId> 40 <version>1.18.16</version> 41 <scope>provided</scope> 42 </dependency> 43 </dependencies> 44 45 <build> 46 <resources> 47 <resource> 48 <directory>src/main/resources</directory> 49 <filtering>true</filtering> 50 <includes> 51 <include>**/*.xml</include> 52 <include>**/*.properties</include> 53 </includes> 54 </resource> 55 <resource> 56 <directory>src/main/java</directory> 57 <filtering>true</filtering> 58 <includes> 59 <include>**/*.xml</include> 60 <include>**/*.properties</include> 61 </includes> 62 </resource> 63 </resources> 64 </build> 65 </project>
2、resources目录下编写jdbc.properties文件
1 mysql.driver=com.mysql.cj.jdbc.Driver 2 mysql.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=CTT 3 mysql.userName=root 4 mysql.password=root
3、编写DButils工具类
package com.sheep.utils; import java.io.IOException; import java.io.InputStream; import java.sql.*; import java.util.Properties; public class DBUtils { // 公共的两部分：1建立连接、2资源释放 // 想要程序一运行就能建立连接，连接需要的东西都写成static静态的，最先执行的。 private static String driver; private static String url; private static String userName; private static String password; static { Properties p = new Properties(); try { InputStream inputStream = DBUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"); p.load(inputStream); driver = p.getProperty("mysql.driver"); url = p.getProperty("mysql.url"); userName = p.getProperty("mysql.userName"); password = p.getProperty("mysql.password"); // 加载驱动 Class.forName(driver); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } // 建立连接 public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url, userName, password); } // 2.释放资源 public static void close(ResultSet rs, PreparedStatement pst, Connection con) throws SQLException { close(rs); close(pst); close(con); } private static void close(Connection con) throws SQLException { if (con != null) { con.close(); } } private static void close(PreparedStatement pst) throws SQLException { if (pst != null) { pst.close(); } } private static void close(ResultSet rs) throws SQLException { if (rs != null) { rs.close(); } } }
4、编写测试类
public class AppTest { @Test public void testB() throws Exception { // 建立连接 Connection con = DBUtils.getConnection(); // 预处理sql语句 PreparedStatement pst = con.prepareStatement("select * from t_student limit 5"); // 执行sql语句 ResultSet rs = pst.executeQuery(); while (rs.next()) { System.out.println(rs.getInt("age") + "==" + rs.getString("name")); } DBUtils.close(rs, pst, con); } }
5、编写BaseDao.java
1 public class BaseDao<T> { 2 3 public List<T> executeQuery(String sql, Class clz) throws Exception { 4 List<T> list = new ArrayList<T>(); 5 // 建立连接 6 Connection con = DBUtils.getConnection(); 7 // 预处理sql语句 8 PreparedStatement pst = con.prepareStatement(sql); 9 // 执行sql语句 10 ResultSet rs = pst.executeQuery(); 11 // 处理执行sql的结果 12 T t; 13 while (rs.next()) { 14 // 反射 15 t = (T) clz.newInstance(); 16 // 获取对象的所有属性 17 Field[] fields = clz.getDeclaredFields(); 18 for (Field f : fields) { 19 f.setAccessible(true); 20 f.set(t, rs.getObject(f.getName())); 21 f.setAccessible(false); 22 } 23 list.add(t); 24 } 25 DBUtils.close(rs, pst, con); 26 return list; 27 } 28 }
6、编写具体实现测试
1 package com.sheep.dao; 2 3 import com.sheep.domain.Student; 4 import com.sheep.utils.BaseDao; 5 6 import java.util.List; 7 8 public class StudentDao extends BaseDao<Student> { 9 10 // 定义list()方法 11 public List<Student> list() throws Exception { 12 // 需要执行的sql语句，以及查询的所属对象 13 // 返回 调用父类的executeQuery()方法 获得的返回值 14 return super.executeQuery("select * from t_student limit 5", Student.class); 15 } 16 17 public static void main(String[] args) throws Exception { 18 19 StudentDao studentDao = new StudentDao(); 20 List<Student> list = studentDao.list(); 21 for (Student s : list) { 22 System.out.println(s); 23 } 24 } 25 }
