day01-Mysql数据库-编写JDBC工具类

Mysql数据库-编写JDBC工具类

我自己写了三个JDBC工具类

工具类一(该工具类没有读properties文件)

package com.ginger.cxy.tool;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


/**
 * JDBC工具类
 * @author Ginger
 * @date 2020年3月2日
 * @version 1.0
 */
public class JDBCUilts_V1 {
	/**
	 * 获取连接方法
	 * @return 
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public static Connection getConnection() throws ClassNotFoundException, SQLException{
		//注册连接
		Class.forName("com.mysql.jdbc.Driver");
		//获取连接
		return DriverManager.getConnection("jdbc:mysql://localhost:3306/sqllx","root","root");
		
	}
	/**
	 * 释放内存方法
	 * @throws SQLException 
	 * 
	 * 
	 */
	public static void release(Connection conn,PreparedStatement ps,ResultSet rs) throws SQLException{
		//判断上面变量是否为空
		if(conn!=null){
			conn.close();
		}
		if(ps!=null){
			ps.close();
		}
		if(rs!=null){
			rs.close();
		}
	}	
}

工具类二(使用ResourceBundle读取properties文件)

package com.ginger.cxy.tool;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;

/**
 * JDBC工具类
 * 
 * @author Ginger
 * @date 2020年3月2日
 * @version 2.0
 */
public class JDBCUtils_V2 {
	public static String driver;
	public static String url;
	public static String username;
	public static String password;
	
	//静态代码块是随着类的加载而加载
	static {
		ResourceBundle rb = ResourceBundle.getBundle("db");
		driver = rb.getString("driver");
		url = rb.getString("url");
		username = rb.getString("username");
		password = rb.getString("password");
	}
	/**
	 * 获取连接
	 * 
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnection() throws Exception {
		Class.forName(driver);
		return DriverManager.getConnection(url, username, password);
	}

	/**
	 * 释放方法
	 * 
	 */
	public static void release(Connection conn, PreparedStatement ps, ResultSet rs) {
		// 判断对象是否为空
		try {
			if (conn != null) {
				conn.close();
			}
			if (conn != null) {
				ps.close();
			}
			if (conn != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

工具类三(使用Classloader类加载读取properties文件)

package com.ginger.cxy.tool;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

/**
 * 编写一个JDBC工具类
 * @author Ginger
 * @date 2020年3月2日
 * @version 3.0
 */
public class JDBCUtils_V3 {
	
	public static String driver;
	public static String url;
	public static String username;
	public static String password;
	//静态代码框是随着类的加载而加载
	static {
		try {
			// 通过当前类获取类加载器
			ClassLoader cl = JDBCUtils_V3.class.getClassLoader();
			// 通过类加载器获取一个输入流
			InputStream is = cl.getResourceAsStream("db.properties");
			// 创建一个Properties对象
			Properties p = new Properties();
			//加载输入流
			p.load(is);
			driver = p.getProperty("driver");
			url = p.getProperty("url");
			username = p.getProperty("username");
			password = p.getProperty("password");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 编写获取连接的方法
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Connection getConnection() throws ClassNotFoundException, SQLException{
		Class.forName(driver);
		return DriverManager.getConnection(url,username,password);
	}
	
	public static void release(Connection conn,PreparedStatement ps,ResultSet rs){
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}	
		}
		if(ps!=null){
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

测试工具类

package com.ginger.cxy.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import com.ginger.cxy.tool.JDBCUilts_V1;
import com.ginger.cxy.tool.JDBCUtils_V2;
import com.ginger.cxy.tool.JDBCUtils_V3;

/**
 * JDBC测试类
 * 
 * @author Ginger
 * @date 2020年3月2日
 * @version 1.0
 */
public class TestLogin {
	
	/**
	 * 测试JDBC工具类V1
	 */
	@Test
	public void testLogin() {
		// 获取连接
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUilts_V1.getConnection();
			String sql = "select * from new_user";
			// 获取执行sql预处理对象
			ps = conn.prepareStatement(sql);
			// 执行sql语句
			rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getString(2) + "," + rs.getString(3));
			}
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				JDBCUilts_V1.release(conn, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}
	/**
	 * 测试JDBC工具类V2
	 * @throws Exception 
	 */
	@Test
	public void testLogin2() throws Exception{
		// 获取连接
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils_V2.getConnection();
			String sql = "select * from new_user";
			// 获取执行sql预处理对象
			ps = conn.prepareStatement(sql);
			// 执行sql语句
			rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getString(2) + "," + rs.getString(3));
			}
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				JDBCUtils_V2.release(conn, ps, rs);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 测试JDBC工具类V3
	 * @throws Exception 
	 */
	@Test
	public void testLogin3() throws Exception{
		// 获取连接
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils_V3.getConnection();
			String sql = "select * from new_user";
			// 获取执行sql预处理对象
			ps = conn.prepareStatement(sql);
			// 执行sql语句
			rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getString(2) + "," + rs.getString(3));
			}
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				JDBCUtils_V3.release(conn, ps, rs);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 测试JDBC工具类V3 	添加
	 * @throws Exception 
	 */
	@Test
	public void testLogin3Insert() throws Exception{
		// 获取连接
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils_V3.getConnection();
			String sql = "insert into new_user values(null,?,?)";
			// 获取执行sql预处理对象
			ps = conn.prepareStatement(sql);
			//设置占位符
			ps.setString(2,"xiaoming");
			ps.setString(3,"xiaoming");
			// 执行sql语句
			int rows = ps.executeUpdate();
			if(rows>0){
				System.out.println("添加成功");
			}else{
				System.out.println("添加失败");
			}
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				JDBCUtils_V3.release(conn, ps, rs);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

}