JAVA之JDBC工具类-Mysql（进阶版）

曾经终败给现在 2022-03-27 09:40 648阅读 0赞

接着上一次**JAVA之JDBC工具类简单版**，新手的话可以先看看[简单版][Link 1]（点击简单版即可跳转的简单版博客）再回来看这个进阶版。还是那句话，不对之处还请各位不吝赐教。

在开始之前，在项目中引入**mysql的资源包**，否则后面的代码会出现很多报错！**务必引入**，该包自行百度下载即可  
![20190112234952418.JPG][]

在项目的目录下创建**config文件夹**，在里面新建文件保存**jdbc的配置信息**，这样做便于修改  
![20190112233336928.PNG][]

**jdbc.properties内容**：

driver=com.mysql.jdbc.Driver
    url=jdbc:mysql://localhost:3306/xianfeng?useUnicode=true&characterEncoding=utf8
    user=root
    password=root

**xianfeng就是对应数据库的名字  
用户和密码要改成自己的哦**  
![20190112233534490.PNG][]

在src下新建一个util包，分别对应四个类**Config,DateUtils,jdbcTest,jdbcUtils**  
![20190112233737523.JPG][]  
**Config.java用于获取配置信息  
DateUtils.java用于将字符串的日期转化问Date格式，这样才能插入数据库  
jdbcTest.java后面用于增删改查的测试  
jdbcUtils.java用于数据库方法的封装(重点)**

**Config.java代码**：

package util;
    
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.Properties;
    
    public class Config {
    private Properties prop = new Properties();
    	
    	public Config(String file)
    	{
    		try
    		{
    			InputStream in = new FileInputStream(file);
    			prop.load(in);
    		} 
    		catch (IOException e) 
    		{
    			e.printStackTrace();
    		}
    	}
    	public String getValue(String key)
    	{
    		return prop.getProperty(key);
    	}
    }

**DateUtils.java代码**：

package util;
    
    import java.text.DateFormat;
    import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    
    public class DateUtils {
    	private static DateFormat d = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
    	private static DateFormat d2 = new SimpleDateFormat("yyyy-MM-dd");
    	
    	public static String getDateStr() {
    		return d.format(new Date());
    	}
    
    	public static String getDateStr(Date date) {
    		return d.format(date);
    	}
    
    	public static Date getDate(String dateStr) {
    		try {
    			Date date = d2.parse(dateStr);
    			return date;
    		} catch (ParseException e) {
    			e.printStackTrace();
    		}
    		return null;
    	}
    
    }

**jdbcUtils代码**：

package util;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ArrayList;
    import java.util.List;
    
    import rowsMapper.RowsMapper;
    
    
    
    public class JdbcUtils {
    	
    	private static String driverClass ;
    	private static String url ;
    	private static String user ;
    	private static String password ;
    	
    	static{
    		Config config = new Config("config/jdbc.properties");
    		
    		driverClass = config.getValue("driver");
    		url = config.getValue("url");
    		user = config.getValue("user");
    		password = config.getValue("password");
    		
    		/**
             * 驱动注册
             */
    		try {
    			Class.forName(driverClass);//注册加载驱动
    		} catch (ClassNotFoundException e) {
    			e.printStackTrace();
    		}
    	}
    	
    	/**
         * 获取 Connetion
         * @return
         * @throws SQLException
         */
    	
    	public static Connection getConnection() throws SQLException{
    		Connection conn = DriverManager.getConnection(url, user, password);
    		return conn;
    	}
    	
    	/**
    	 * 关闭ResultSet资源
    	 * @param rs
    	 */
    	
    	public static void close(ResultSet rs){
    		if(null != rs){
    			try {
    				rs.close();
    			} catch (SQLException e) {
    				e.printStackTrace();
    			}
    		}
    	}
    	
    	/**
    	 * 关闭Statement资源
    	 * @param stmt
    	 */
    	
    	public static void close(Statement stmt){
    		if(null != stmt){
    			try {
    				stmt.close();
    			} catch (SQLException e) {
    				e.printStackTrace();
    			}
    		}
    	}
    	
    	/**
    	 * 关闭close资源
    	 * @param conn
    	 */
    	public static void close(Connection conn){
    		if(null != conn){
    			try {
    				conn.close();
    			} catch (SQLException e) {
    				e.printStackTrace();
    			}
    		}
    	}
    	/**
    	 * 关闭资源
    	 * @param rs
    	 * @param stmt
    	 * @param conn
    	 */
    	
    	public static void close(ResultSet rs,Statement stmt,Connection conn){
    		close(rs);
    		close(stmt);
    		close(conn);
    	}
    	/**
    	 * insert方法
    	 * @param conn
    	 * @param sql
    	 * @param parems
    	 * @return
    	 * @throws SQLException
    	 */
    	public static int insert(Connection conn,String sql,Object[] parems) throws SQLException{
    		PreparedStatement stmt = null;
    		try{
    			stmt = conn.prepareStatement(sql);
    			if(null != parems && parems.length>0){//判断是否有信息增加
    				for(int i = 0;i<parems.length;i++){
    					stmt.setObject(i+1, parems[i]);//使用给定对象设置指定参数的值
    				}
    			}
    			int count = stmt.executeUpdate();
    			
    			return count;
    		}finally{
    			close(stmt);
    		}
    	}
    	/**
    	 * 泛型查询方法
    	 * @param conn
    	 * @param sql
    	 * @param paramsList
    	 * @param rm
    	 * @return
    	 * @throws SQLException
    	 */
    	public static <T> List<T> executeQuery(Connection conn,String sql,Object[] paramsList,RowsMapper<T> rm) throws SQLException{
    		PreparedStatement stmt = null;
    		ResultSet rs = null;
    		List<T> list = new ArrayList<T>(); 
    		try{
    			stmt = conn.prepareStatement(sql);
    			if(null != paramsList && paramsList.length>0){//判断是否有信息增加
    				for(int i = 0;i<paramsList.length;i++){
    					stmt.setObject(i+1, paramsList[i]);
    				}
    			}
    			rs = stmt.executeQuery();
    			while(rs.next()){
    				T t = rm.getEntity(rs);
    				list.add(t);
    			}
    			return list;
    		}finally{
    			close(rs);
    			close(stmt);
    		}
    	}
    }

在项目下新建一个**entity包**，里面**放实体类Doctor**  
![20190112235349741.JPG][]  
**Doctor.java代码**：

package entity;
    
    import java.util.Date;
    
    public class Doctor {
    	private int id ;
    	//医生编号
    	private String doctorNo;
    	//医生姓名
    	private String doctorName;
    	//医生性别
    	private String doctorSex;
    	//医生电话
    	private String doctorTel;
    	//科室名字
    	private String sectionName;
    	//医生学历
    	private String doctorEduction;
    	//医生简介
    	private String doctorIntroduce;
    	//医生入职时间
    	private Date dootorHiredate;
    	//医生年龄
    	private int doctorAge;
    	//医生身份证号
    	private String doctorPcard;
    	public int getId() {
    		return id;
    	}
    	public void setId(int id) {
    		this.id = id;
    	}
    	public String getDoctorNo() {
    		return doctorNo;
    	}
    	public void setDoctorNo(String doctorNo) {
    		this.doctorNo = doctorNo;
    	}
    	public String getDoctorName() {
    		return doctorName;
    	}
    	public void setDoctorName(String doctorName) {
    		this.doctorName = doctorName;
    	}
    	public String getDoctorSex() {
    		return doctorSex;
    	}
    	public void setDoctorSex(String doctorSex) {
    		this.doctorSex = doctorSex;
    	}
    	public String getDoctorTel() {
    		return doctorTel;
    	}
    	public void setDoctorTel(String doctorTel) {
    		this.doctorTel = doctorTel;
    	}
    	public String getSectionName() {
    		return sectionName;
    	}
    	public void setSectionName(String sectionName) {
    		this.sectionName = sectionName;
    	}
    	public String getDoctorEduction() {
    		return doctorEduction;
    	}
    	public void setDoctorEduction(String doctorEduction) {
    		this.doctorEduction = doctorEduction;
    	}
    	public String getDoctorIntroduce() {
    		return doctorIntroduce;
    	}
    	public void setDoctorIntroduce(String doctorIntroduce) {
    		this.doctorIntroduce = doctorIntroduce;
    	}
    	public Date getDootorHiredate() {
    		return dootorHiredate;
    	}
    	public void setDootorHiredate(Date dootorHiredate) {
    		this.dootorHiredate = dootorHiredate;
    	}
    	public int getDoctorAge() {
    		return doctorAge;
    	}
    	public void setDoctorAge(int doctorAge) {
    		this.doctorAge = doctorAge;
    	}
    	public String getDoctorPcard() {
    		return doctorPcard;
    	}
    	public void setDoctorPcard(String doctorPcard) {
    		this.doctorPcard = doctorPcard;
    	}
    }

继续在项目下面新建**rowsMapper包和服务层的两个包server.dao，server.dao.impl**  
![20190113234535814.JPG][]  
**rowsMapper**主要是将**数据结果rs中的属性赋值给对象，然后返回该对象**  
**server.dao，server.dao.impl**主要用于**封装对象的增删改查方法**  
**rowsMapper包中RowsMapper代码**：

package rowsMapper;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    /**
     * 转换为行对象的接口
     * @param <T>
     */
    public interface RowsMapper<T> {
    	public T getEntity(ResultSet rs) throws SQLException;
    
    }

**rowsMapper包中DoctorRowsMapper代码**：

package rowsMapper;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    import entity.Doctor;
    
    public class DoctorRowsMapper implements RowsMapper<Doctor> {
    public Doctor getEntity(ResultSet rs) throws SQLException{
    		
    		Doctor doctor = new Doctor();
    		doctor.setId(rs.getInt("D_ID"));
    		doctor.setDoctorNo(rs.getString("D_No"));
    		doctor.setDoctorName(rs.getString("D_Name"));
    		doctor.setDoctorSex(rs.getString("D_Sex"));
    		doctor.setDoctorTel(rs.getString("D_Tel"));
    		doctor.setSectionName(rs.getString("S_Name"));
    		doctor.setDoctorEduction(rs.getString("D_Education"));
    		doctor.setDoctorIntroduce(rs.getString("D_Introduce"));
    		doctor.setDootorHiredate(rs.getDate("D_hiredate"));
    		doctor.setDoctorAge(rs.getInt("D_Age"));
    		doctor.setDoctorPcard(rs.getString("D_Pcard"));
    
    		return doctor;
    	}
    
    }

**server.dao包中IDoctorDao代码**：

package server.dao;
    
    import entity.Doctor;
    
    public interface IDoctorDao {
    	/**
    	 * 根据登陆账号找到医生信息
    	 */
    	public Doctor queryByDoctorNo(String DoctorNo) ;
    	
    	/**
    	 * 添加员工
    	 */
    	public void add(Doctor doctorEntity);
    	/**
    	 * 根据ID删除信息
    	 */
    	public void delete(int[] id);
    	/**
    	 * 修改信息
    	 */
    	public void edit(Doctor doctorEntity);
    }

**server.dao.impl包中DoctorDaoImpl代码**：

package server.dao.impl;
    
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.List;
    
    import entity.Doctor;
    import rowsMapper.DoctorRowsMapper;
    import rowsMapper.RowsMapper;
    import server.dao.IDoctorDao;
    import util.JdbcUtils;
    
    public class DoctorDaoImpl implements IDoctorDao {
    
    	@Override
    	public Doctor queryByDoctorNo(String DoctorNo) {
    		Connection conn = null;
    		try {
    			conn = JdbcUtils.getConnection();
    			String sql = "select * from t_doctor where D_No = ?";
    			Object[] paramsList= {DoctorNo};
    			RowsMapper<Doctor> rm = new DoctorRowsMapper();
    			List<Doctor> list = JdbcUtils.executeQuery(conn, sql, paramsList, rm);
    			if(null !=list &&list.size() > 0) {
    				return list.get(0);
    			}
    		} catch (SQLException e) {
    			// TODO 自动生成的 catch 块
    			e.printStackTrace();
    		}finally {
    			JdbcUtils.close(conn);
    		}
    		return null;
    	}
    
    	@Override
    	public void add(Doctor doctorEntity) {
    		Connection conn = null;
    		try {
    			conn = JdbcUtils.getConnection();
    			String sql = "insert into t_doctor (D_ID,D_No,D_Name,D_Age,D_Sex,D_Tel,D_Pcard,S_Name,D_Education,D_Introduce,D_hiredate)";
    			sql += " value (?,?,?,?,?,?,?,?,?,?,?)";
    			Object[] params = { null,doctorEntity.getDoctorNo(),
    					doctorEntity.getDoctorName(), doctorEntity.getDoctorAge(),
    					doctorEntity.getDoctorSex(), doctorEntity.getDoctorTel(),
    					doctorEntity.getDoctorPcard(), doctorEntity.getSectionName(),
    					doctorEntity.getDoctorEduction(),doctorEntity.getDoctorIntroduce(),doctorEntity.getDootorHiredate() };
    			JdbcUtils.insert(conn, sql, params);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		} finally {
    			JdbcUtils.close(conn);
    		}
    		
    	}
    
    	@Override
    	public void delete(int[] id) {
    		Connection conn = null;
    		try {
    			conn = JdbcUtils.getConnection();
    			String sql = "delete from t_doctor where 1 = 1";
    			sql += " and D_ID in (";
    			Object[] params = new Object[id.length];
    			for (int i = 0; i < id.length; i++) {
    				if (i == id.length - 1) {
    					sql += " ?";
    				} else {
    					sql += " ?,";
    				}
    				params[i] = id[i];
    			}
    			sql += " )";
    			JdbcUtils.insert(conn, sql, params);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		} finally {
    			JdbcUtils.close(conn);
    		}
    		
    	}
    
    	@Override
    	public void edit(Doctor doctorEntity) {
    		Connection conn = null;
    		try {
    			conn = JdbcUtils.getConnection();
    			String sql = "update t_doctor set D_ID = ?,D_No = ?,D_Name = ?,"
    					+ "D_Age = ?,D_Sex = ?,D_Tel = ?,D_Pcard = ?,S_Name = ?,"
    					+ "D_Education = ?,D_Introduce = ?,D_hiredate = ? where D_ID = ?";
    			Object[] params = { doctorEntity.getId(),doctorEntity.getDoctorNo(),doctorEntity.getDoctorName(),
    					doctorEntity.getDoctorAge(),doctorEntity.getDoctorSex(),
    					doctorEntity.getDoctorTel(),doctorEntity.getDoctorPcard(),
    					doctorEntity.getSectionName(),doctorEntity.getDoctorEduction(),
    					doctorEntity.getDoctorIntroduce(),doctorEntity.getDootorHiredate(),
    					doctorEntity.getId()};
    			JdbcUtils.insert(conn, sql, params);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		} finally {
    			JdbcUtils.close(conn);
    		}
    		
    	}
    }

**server.dao**中的思路主要是在**对象接口中声明对象的数据库方法**，这里就举例Doctor对象的增删改查，然后在**server.dao.impl**中**实现对应接口的方法**，后面在**jdbcTest**通过**接口回调**即可调用写好的方法

接下来就是在jdbcTest进行测试

**查询测试**：

**测试结果**：  
![20190113233257415.JPG][]  
**数据库**：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNDMyMw_size_16_color_FFFFFF_t_70][]  
**增加测试（插入新数据）**：

package util;
    
    import entity.Doctor;
    import server.dao.IDoctorDao;
    import server.dao.impl.DoctorDaoImpl;
    
    public class jdbcTest {
    	
    	
    	public static void main(String[] args) {
    		IDoctorDao doctorDao = new DoctorDaoImpl();
    		Doctor doctor = new Doctor();
    		doctor.setDoctorNo("a005");
    		doctor.setDoctorName("小白");
    		doctor.setDoctorAge(24);
    		doctor.setDoctorSex("男");
    		doctor.setDoctorTel("66666668888");
    		doctor.setDoctorPcard("441381199911113344");
    		doctor.setSectionName("儿科");
    		doctor.setDoctorEduction("本科");
    		doctor.setDoctorIntroduce("是一个程序员");
    		doctor.setDootorHiredate(DateUtils.getDate("2019-1-10"));
    		doctorDao.add(doctor);
    	    }
    	}

**数据库**：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNDMyMw_size_16_color_FFFFFF_t_70 1][]  
**删除测试**：

package util;
    
    
    import server.dao.IDoctorDao;
    import server.dao.impl.DoctorDaoImpl;
    
    public class jdbcTest {
    	
    	
    	public static void main(String[] args) {
    		IDoctorDao doctorDao = new DoctorDaoImpl();
    		int a[] = {4};
    		doctorDao.delete(a);
    	    }
    	}

**数据库**：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNDMyMw_size_16_color_FFFFFF_t_70 2][]

**修改测试**：

数据库：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNDMyMw_size_16_color_FFFFFF_t_70 3][]

从数据库的结果图都可以看出测试都是**通过的**，不管有没看懂都敲一遍吧，这样你基本就掌握了**JDBC**的**增删改查**了，还是挺实用的呢

[Link 1]: https://blog.csdn.net/weixin_42404323/article/details/84801004
[20190112234952418.JPG]: /images/20220327/f4ec82b90e9e407ea662530bf70aed42.png
[20190112233336928.PNG]: /images/20220327/74fac07c126845c7a62bea9e28a70904.png
[20190112233534490.PNG]: /images/20220327/6b8be05f466d4c66a4aa15a29d9f0db8.png
[20190112233737523.JPG]: /images/20220327/9211ce147b04404db38560cab7eb77c1.png
[20190112235349741.JPG]: /images/20220327/b8a8a79bcbcb4fd292792dea4bb823c4.png
[20190113234535814.JPG]: /images/20220327/4739062aee3f4baa860b0140084c1528.png
[20190113233257415.JPG]: /images/20220327/44bb4aa4ecfd46dda143df10bc929d25.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNDMyMw_size_16_color_FFFFFF_t_70]: /images/20220327/1cb5f43f2997488da0987283f27d3d3e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNDMyMw_size_16_color_FFFFFF_t_70 1]: /images/20220327/9c9897ec14764099867ca279b6060abf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNDMyMw_size_16_color_FFFFFF_t_70 2]: /images/20220327/5c6765cc8d004acc9d992e59fc6aba4b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNDMyMw_size_16_color_FFFFFF_t_70 3]: /images/20220327/af0bcf6ae08b4d20b2868c0770e3d26e.png