jdbc基础与工具类封装

谁践踏了优雅 2023-10-18 23:53 202阅读 0赞

我的网站：欢迎大家访问

aHR0cHM6Ly9zMi5heDF4LmNvbS8yMDE5LzA4LzEzL21DcWJaVC5wbmc

JDBC

概念

JDBC(Java DataBase Connectivity（Java数据库的连接）):

是一种用于执行SQL语句(DML,DDL,DQL)的Java API，可以为多种关系数据库（oracle,mysql,SQL server）提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序.

不管使用哪一种数据库，都需要去导入这个数据库给我们提供的jdbc实现的jar包

访问数据库的两种形式

1.JDBC最原生的方式去访问

2.使用第三方框架来进行访问(底层依然是JDBC)

JDBC连接数据库

导入相应的jar包

jdbc连接数据库步骤：贾琏欲执事

1.加载驱动

2.建立连接

3.获取语句

4.执行语句

5.关闭连接，释放资源

加载驱动三种方式

这里介绍最常用的一种：

  1. try {
  3.             //第一步：通过反射加载驱动
  4.             Class.forName("com.mysql.jdbc.Driver");
  5.             //第二步：建立连接
  6.             Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbcdemo","root","594395fzk");
  7.             //第三步：获取语句对象,建表,书写sql语句
  8.             Statement statement = conn.createStatement();
  9.             String sql = "create table student(id bigint(20),name varchar(255),age int(10))";
  10.             //第四步：执行sql语句
  11.             //boolean b = statement.execute(sql);
  12.             String ins = "insert into student (id,name,age) value ('02','电棍','2')";
  13.             statement.executeUpdate(ins);
  14.             //第五步：释放资源，关闭数据库连接
  15.             statement.close();
  16.             conn.close();
  17.         } catch (Exception e) {
  19.             // TODO Auto-generated catch block
  20.             e.printStackTrace();
  21.         }

至于其他两种：?????百度一下，你就知道

使用JDBC实现增删改查

  1. package com.ifueen.classtest.jdbcdemo;
  3. import static org.junit.Assert.*;
  5. import java.sql.Connection;
  6. import java.sql.DriverManager;
  7. import java.sql.ResultSet;
  8. import java.sql.SQLException;
  9. import java.sql.Statement;
  11. import org.junit.After;
  12. import org.junit.Before;
  13. import org.junit.Test;
  15. public class JdbcConnect {
  17.     Connection conn;
  18.     Statement statement;
  19.     @Before
  20.     public void before() {
  22.         try {
  24.             //加载启动
  25.             Class.forName("com.mysql.jdbc.Driver");
  26.             //连接数据库
  27.             conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbcdemo","root","594395fzk");
  28.         } catch (Exception e) {
  30.             // TODO Auto-generated catch block
  31.             e.printStackTrace();
  32.         }
  33.     }
  34.     /**
  35.      * @throws Exception
  36.      * 添加数据
  37.      */
  38.     @Test
  39.     public void insert() throws Exception {
  41.         String ins = "insert into student (id,name,age) values (5,'窦唯',20)";
  42.         statement = conn.createStatement();
  43.         statement.execute(ins);
  44.     }
  45.     /**
  46.      * @throws Exception
  47.      * 删除数据
  48.      */
  49.     @Test
  50.     public void delete() throws Exception {
  52.         String del = "delete from student where id=5";
  53.         statement = conn.createStatement();
  54.         statement.execute(del);
  55.     }
  56.     /**
  57.      * @throws Exception
  58.      * 更新数据
  59.      */
  60.     @Test
  61.     public void update() throws Exception {
  63.         String update = "update student set name='小小山泥若' where id=1";
  64.         statement = conn.createStatement();
  65.         statement.executeUpdate(update);
  66.     }
  67.     /**
  68.      * @throws Exception
  69.      * 查询数据
  70.      */
  71.     @Test
  72.     public void select() throws Exception {
  74.         String select = "select * from student";
  75.         statement = conn.createStatement();
  76.         ResultSet resultSet = statement.executeQuery(select);
  77.         while(resultSet.next()){
  79.             System.out.println(resultSet.getString("name"));
  80.         }
  81.     }
  82.     /**
  83.      * @throws SQLException
  84.      * 关闭连接，释放资源
  85.      */
  86.     @After
  87.     public void after() throws SQLException{
  89.         statement.close();
  90.         conn.close();
  91.     }
  93. }

封装JDBC工具类

为了更方便，让代码使用起来更高效，可以将连接数据库的操作封装成一个工具类，以后在每次要进行数据库连接的时候直接使用工具类即可

这里先将数据库的驱动连接，url，账户密码等放在了资源文件夹的properties文件中

资源文件db.properties:

  1. driverClassName=com.mysql.jdbc.Driver
  2. url=jdbc:mysql://127.0.0.1:3306/jdbcwork
  3. username=root
  4. password=594395fzk

封装的工具类:

  1. package com.ifueen.classtest.util;
  3. import java.io.IOException;
  4. import java.sql.Connection;
  5. import java.sql.DriverManager;
  6. import java.sql.ResultSet;
  7. import java.sql.SQLException;
  8. import java.sql.Statement;
  9. import java.util.Properties;
  11. public class JDBCUtil {
  13.     //创建单例模式
  14.     private static JDBCUtil instance;
  15.     private JDBCUtil() {
  16.     }
  17.     public static JDBCUtil getinstance() {
  19.         return instance;
  20.     }
  21.     private static Properties prop = new Properties();
  22.     //静态代码块，存放要优先加载的功能语句
  23.     static{
  25.         try {
  27.             //通过字节码来获取流
  28.             prop.load(JDBCUtil.class.getResourceAsStream("/db.properties"));
  29.         } catch (IOException e1) {
  31.             // TODO Auto-generated catch block
  32.             e1.printStackTrace();
  33.         }
  34.         instance = new JDBCUtil();
  35.         try {
  37.             //加载驱动
  38.             Class.forName(prop.getProperty("driverClassName"));
  39.         } catch (Exception e) {
  41.             e.printStackTrace();
  42.         }
  43.     }
  45.     /**
  46.      * @return
  47.      * 连接数据库的方法
  48.      */
  49.     public  Connection getconn() {
  51.          Connection conn = null;
  52.         try {
  54.             //连接数据库
  55.             conn = DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password"));
  56.         } catch (Exception e) {
  58.             // TODO Auto-generated catch block
  59.             e.printStackTrace();
  60.         }
  61.         return conn;
  62.     }
  64.     /**
  65.      * 
  66.      * 关闭连接，释放资源
  67.      */
  68.     public  void getclose(ResultSet rs, Statement st, Connection conn) {
  70.         try {
  72.             if (rs != null)
  73.                 rs.close();
  74.         } catch (SQLException e) {
  76.             // TODO Auto-generated catch block
  77.             e.printStackTrace();
  78.         } finally {
  81.             try {
  83.                 if (st != null)
  84.                     st.close();
  85.             } catch (SQLException e) {
  87.                 // TODO Auto-generated catch block
  88.                 e.printStackTrace();
  89.             } finally {
  92.                 try {
  94.                     if (conn != null)
  95.                         conn.close();
  96.                 } catch (SQLException e) {
  98.                     // TODO Auto-generated catch block
  99.                     e.printStackTrace();
  100.                 }
  101.             }
  102.         }
  103.     }
  104.     public  void getclose(Statement st, Connection conn) {
  106.             try {
  108.                 if(st!=null)
  109.                 st.close();
  110.             } catch (SQLException e) {
  112.                 // TODO Auto-generated catch block
  113.                 e.printStackTrace();
  114.             }finally {
  116.                 if(conn!=null)
  117.                     try {
  119.                         conn.close();
  120.                     } catch (SQLException e) {
  122.                         // TODO Auto-generated catch block
  123.                         e.printStackTrace();
  124.                     }
  125.             }
  126.     }
  127. }

这下连接数据库的操作就很好地封装成了一个工具类，只需要在要进行操作的地方使用即可

测试类：

  1. package com.ifueen.classtest.util;
  3. import static org.junit.Assert.*;
  5. import java.sql.Connection;
  6. import java.sql.ResultSet;
  7. import java.sql.SQLException;
  8. import java.sql.Statement;
  10. import org.junit.Test;
  12. public class UtilTest {
  15.     @Test
  16.     public void test() throws Exception {
  18.         //调用封装好的加载驱动的方法
  19.         Connection conn = JDBCUtil.getinstance().getconn();
  20.         Statement st = conn.createStatement();
  21.         String sql = "select * from student";
  22.         ResultSet rs = st.executeQuery(sql);
  23.         while (rs.next()) {
  25.             System.out.println(rs.getString("name"));
  26.         }
  27.         //调用关闭驱动的方法
  28.         JDBCUtil.getinstance().getclose(rs, st, conn);
  29.     }
  31. }

Dao层的实现

DAO : 主要是用来做数据库的操作
模块化,分工明确,解耦(责任分离)

刚才已经说到要分层，各层之间相互调用，现在数据层 调用 数据库，需要传什么内容?
比如 要添加一个用户，怎么添加?

界面层：收集数据； name，password，age…等等 需要传递很多东西;
业务层：界面层收集之后，传到业务层;

在这里插入图片描述
因为我们没有前端传过来的数据，现在只使用dao,domian层来进行crud操作

大体结构如下
在这里插入图片描述

首先肯定要在数据库中将表建好，然后写一个javabean类和这张表一一对应

在这里插入图片描述

domian层下面的javabean类：

  1. package com.ifueen.homework.domian;
  3. public class Student {
  5.     private long id;
  6.     private String username;
  7.     private String password;
  8.     private int age;
  9.     private boolean sex;
  10.     private String intro;
  11.     public long getId() {
  13.         return id;
  14.     }
  15.     public void setId(long id) {
  17.         this.id = id;
  18.     }
  19.     public String getUsername() {
  21.         return username;
  22.     }
  23.     public void setUsername(String username) {
  25.         this.username = username;
  26.     }
  27.     public String getPassword() {
  29.         return password;
  30.     }
  31.     public void setPassword(String password) {
  33.         this.password = password;
  34.     }
  35.     public int getAge() {
  37.         return age;
  38.     }
  39.     public void setAge(int age) {
  41.         this.age = age;
  42.     }
  43.     public boolean isSex() {
  45.         return sex;
  46.     }
  47.     public void setSex(boolean sex) {
  49.         this.sex = sex;
  50.     }
  51.     public String getIntro() {
  53.         return intro;
  54.     }
  55.     public void setIntro(String intro) {
  57.         this.intro = intro;
  58.     }
  59.     public Student(long id, String username, String password, int age, boolean sex, String intro) {
  61.         super();
  62.         this.id = id;
  63.         this.username = username;
  64.         this.password = password;
  65.         this.age = age;
  66.         this.sex = sex;
  67.         this.intro = intro;
  68.     }
  69.     public Student(Long id,String username) {
  71.         super();
  72.         this.username = username;
  73.         this.id = id;
  74.     }
  75.     public Student() {
  77.         super();
  78.     }
  80. }

Dao类:

  1. package com.ifueen.homework.dao;
  3. import com.ifueen.homework.domian.Student;
  5. public interface StudentDao {
  7.     //增加数据
  8.     void add(Student s);
  9.     //删除数据
  10.     void delete(long id);
  11.     //修改数据
  12.     void update(Student s);
  13.     //查找数据
  14.     Student select(Student s);
  15.     //查找数据2
  16.     void select(Long id);
  17. }

dao的实现类

  1. package com.ifueen.homework.dao.impl;
  3. import java.sql.Connection;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  8. import com.ifueen.classtest.util.JDBCUtil;
  9. import com.ifueen.homework.dao.StudentDao;
  10. import com.ifueen.homework.domian.Student;
  11. import com.sun.javafx.binding.Logging;
  13. public class StudentDaoImpl implements StudentDao{
  16.     /** 
  17.      * 新增数据
  18.      */
  19.     @Override
  20.     public void add(Student s) {
  22.         // TODO Auto-generated method stub
  23.         try {
  25.             //加载驱动，连接数据库
  26.             Connection conn = JDBCUtil.getinstance().getconn();
  27.             //得到Statement对象
  28.             Statement st = conn.createStatement();
  29.             //写sql语句
  30.             String sql = "insert into student(id,username,password,age,sex,intro) "
  31.                     + "values ('"+s.getId()+"','"+s.getUsername()+"','"+s.getPassword()+"','"+s.getAge()+"','"+s.isSex()+"','"+s.getIntro()+"')";
  32.             //执行sql语句
  33.             st.execute(sql);
  34.             //关闭连接，释放资源
  35.             JDBCUtil.getinstance().getclose(st, conn);
  36.         } catch (SQLException e) {
  38.             // TODO Auto-generated catch block
  39.             e.printStackTrace();
  40.         }
  41.     }
  43.     /**
  44.      * 删除数据
  45.      */
  46.     @Override
  47.     public void delete(long id) {
  49.         // TODO Auto-generated method stub
  50.         try {
  52.             Connection conn = JDBCUtil.getinstance().getconn();
  53.             Statement st = conn.createStatement();
  54.             String sql = "delete from student where id="+id;
  55.             st.execute(sql);
  56.             JDBCUtil.getinstance().getclose(st, conn);
  57.         } catch (SQLException e) {
  59.             // TODO Auto-generated catch block
  60.             e.printStackTrace();
  61.         }
  62.     }
  64.     /** 
  65.      * 修改数据
  66.      */
  67.     @Override
  68.     public void update(Student s) {
  70.         // TODO Auto-generated method stub
  71.         try {
  73.             Connection conn = JDBCUtil.getinstance().getconn();
  74.             Statement st = conn.createStatement();
  75.             String sql = "update student set username='"+s.getUsername()+"' where id="+s.getId();
  76.             st.executeUpdate(sql);
  77.             JDBCUtil.getinstance().getclose(st, conn);
  78.         } catch (SQLException e) {
  80.             // TODO Auto-generated catch block
  81.             e.printStackTrace();
  82.         }
  83.     }
  85.     /**
  86.      * 查询数据1
  87.      */
  88.     @Override
  89.     public Student select(Student s) {
  91.         Student student = null;
  92.         // TODO Auto-generated method stub
  93.         try {
  95.             Connection conn = JDBCUtil.getinstance().getconn();
  96.             Statement st = conn.createStatement();
  97.             String sql = "select * from student";
  98.             ResultSet set = st.executeQuery(sql);
  99.             student = new Student();
  100.             while (set.next()) {
  102.                 student.setId(set.getLong("id"));
  103.                 student.setUsername(set.getString("username"));
  104.                 student.setPassword(set.getString("password"));
  105.                 student.setAge(set.getInt("age"));
  106.                 student.setSex(set.getBoolean("sex"));
  107.                 student.setIntro(set.getString("intro"));
  108.             }
  109.             JDBCUtil.getinstance().getclose(st, conn);
  110.         } catch (SQLException e) {
  112.             // TODO Auto-generated catch block
  113.             e.printStackTrace();
  114.         }
  115.         return student;
  116.     }
  118.     /**
  119.      * 查询数据2
  120.      * 全部循环打印显示出来
  121.      */
  122.     @Override
  123.     public void select(Long id) {
  125.         Student student = null;
  126.         // TODO Auto-generated method stub
  127.         try {
  129.             Connection conn = JDBCUtil.getinstance().getconn();
  130.             Statement st = conn.createStatement();
  131.             String sql = "select * from student where id="+id;
  132.             ResultSet set = st.executeQuery(sql);
  133.             student = new Student();
  134.             while (set.next()) {
  136.                 System.out.print(set.getLong("id")+"  ");
  137.                 System.out.print(set.getString("username")+"  ");
  138.                 System.out.print(set.getString("password")+"  ");
  139.                 System.out.print(set.getInt("age")+"  ");
  140.                 System.out.print(set.getBoolean("sex")+"  ");
  141.                 System.out.print(set.getString("intro")+"  ");
  142.             }
  143.             JDBCUtil.getinstance().getclose(st, conn);
  144.         } catch (SQLException e) {
  146.             // TODO Auto-generated catch block
  147.             e.printStackTrace();
  148.         }
  149.     }
  151. }

最后测试类：

  1. package com.ifueen.homework.test;
  3. import static org.junit.Assert.*;
  5. import java.sql.Connection;
  6. import java.sql.SQLException;
  7. import java.sql.Statement;
  9. import org.junit.Before;
  10. import org.junit.Test;
  12. import com.ifueen.classtest.util.JDBCUtil;
  13. import com.ifueen.homework.dao.StudentDao;
  14. import com.ifueen.homework.dao.impl.StudentDaoImpl;
  15. import com.ifueen.homework.domian.Student;
  17. public class StudentTest {
  19.     StudentDao std = new StudentDaoImpl();
  21.     /**
  22.      * 添加数据
  23.      */
  24.     @Test
  25.     public void add() {
  27.         Student student = new Student(1,"山泥若","123456",25,true,"我顶不住了");
  28.         std.add(student);
  29.     }
  30.     /**
  31.      * 删除数据
  32.      */
  33.     @Test
  34.     public void delete() {
  36.         std.delete(1L);
  37.     }
  38.     /**
  39.      * 修改数据
  40.      */
  41.     @Test
  42.     public void update() {
  44.         Student student = new Student(2L,"落日飞车");
  45.         std.update(student);
  46.     }
  47.     /**
  48.      * 查询数据
  49.      */
  50.     @Test
  51.     public void select() {
  53.         Student student = new Student(1,"山泥若","123456",25,true,"我顶不住了");
  54.         //拿到查询的结果
  55.         Student select = std.select(student);
  56.         System.out.println(select.getUsername());
  57.         System.out.println(select.getIntro());
  58.     }
  59.     /**
  60.      * 查询数据2
  61.      */
  62.     @Test
  63.     public void selects() {
  65.         std.select(1L);
  66.     }
  68. }

发表评论

表情：
评论列表 （有 0 条评论，202人围观）

还没有评论，来说两句吧...

相关阅读

    相关 jdbc 工具类

    最近写了一个jdbc工具类，拿出来献丑了，最近数据库配置都喜欢放在配置文件，我的这个也是。这么做的优势就是，当你去换数据库的时候不用改动代码，例如去现场环境部署代码，和你在测试

    Dear 丶Dear 丶/ 0 赞/ 373 阅读