Java JDBC 示例与最佳实践-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_35287067/article/details/80630511

本文介绍了 Java 中 JDBC 的基本使用方法，包括连接数据库、执行 SQL 语句、处理结果集等操作，并提供了防止 SQL 注入的安全编程示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

package it.cast.demo;

import static org.hamcrest.CoreMatchers.nullValue;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;

public class Demo2 {
	/*
	 * 连接数据库 得到Connection就算成功 对数据库做增删改
	 */
	@Test
	public void fun1() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		// jdbc的格式
		// 对mysql而言 它的子协议结构是: //主机:端口号/数据库名称
		String url = "jdbc:mysql://localhost:3306/mydb1";
		String username = "root";
		String password = "123";
		Connection con = DriverManager.getConnection(url, username, password);

		/*
		 * 1通过Connection对象创建Statement >Statement语句的发送器 他的功能是向数据库发送sql语句
		 * 2.调用它的int executeUpdate(String sql) 它可以发送DML,DDL
		 */
		// 1
		Statement stmt = con.createStatement();
		// 2
		String sql = "INSERT INTO stu VALUES('ITCAST_0004','wangWu',88,'male')";
		// String sql = "UPDATE stu SET name = 'zhaoLiu',age=22,gender='female'
		// WHERE number='ITCAST_0004'";
		// String sql ="DELETE FROM stu";

		int r = stmt.executeUpdate(sql);
		System.out.println(r);

	}

	/*
	 * 执行查询
	 */
	@Test
	public void fun2() throws ClassNotFoundException, SQLException {
		/*
		 * 1.得到Connection 2.得到Statement 发送select语句 3.对查询返回的表格进行解析
		 * 
		 */
		String driverClassName = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/exam";
		String username = "root";
		String password = "123";
		Class.forName(driverClassName);
		Connection con = DriverManager.getConnection(url, username, password);
		Statement stmt = con.createStatement();
		String sql = "";
		// 调用Statement的ResultSet rs = stmt.executeQuery(String querySql)
		ResultSet rs = stmt.executeQuery("SELECT * FROM emp");
		System.out.println(rs);
		// 解析ResultSet
		// 1.把行光标移动到第一行 可以调用next()方法
		while (rs.next()) {
			int empno = rs.getInt(1); // 通过列编号来获取该列的值
			String ename = rs.getString("ename"); // 通过列名称
			double sal = rs.getDouble("sal");

			System.out.println(empno + "..." + ename + "..." + sal);
		}
		// 最后 关闭资源 倒关
		rs.close();
		stmt.close();
		con.close(); // 这个必须关

	}

	// 规范化
	@Test
	public void fun3() throws Exception {
		Connection con = null; // 定义引用
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String driverClassName = "com.mysql.jdbc.Driver";
			String url = "jdbc:mysql://localhost:3306/exam";
			String username = "root";
			String password = "123";
			Class.forName(driverClassName);
			con = DriverManager.getConnection(url, username, password); // 实例化
			stmt = con.createStatement();
			String sql = "select * from emp";
			rs = stmt.executeQuery(sql);
			// getString() 和getObject*()基本通用
//			 while (rs.next()) {
//			 System.out.println(rs.getObject(1) + ", " +
//			 rs.getString("ename")+rs.getDouble("sal"));
//			 }
			int count = rs.getMetaData().getColumnCount();//计算出有多少列
			while (rs.next()) { // 遍历行
				for (int i = 1; i <= count; i++) { // 遍历列 注意这里i最小等于1 
					System.out.print(rs.getString(i));
					if (i < count) {
						System.out.print("...");
					}
				}
				System.out.println();
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (con != null)
				con.close();
			if (stmt != null)
				stmt.close();
			if (rs != null)
				rs.close();
		}

	}
}

加强版

package it.cast.demo;

import java.io.IOException;
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 org.junit.Test;


/**
 * 学习 PreparedStatement
 * 防sql攻击
 * @author 10958
 *
 */
public class Demo3 { 
	
	/**
	 * 登录
	 * 使用username和password去查询数据库
	 * 若查出结果集 则正确true
	 * 这个存在sql攻击 所以用2
	 * @param username
	 * @param password
	 * @return
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public boolean login(String username,String password) throws ClassNotFoundException, SQLException {
		//1得到Connection
		//2得到Statement
		//3得到ResultSet
		//4rs.next()返回的是什么 我们就返回啥
		String driverClassName = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/mydb3";
		String mysqlUsername = "root";
		String mysqlPassword = "123";
		Class.forName(driverClassName);
		Connection con = DriverManager.getConnection(url,mysqlUsername,mysqlPassword);
		Statement stmt =  con.createStatement();
		String sql = "select * from t_user where username='"+username+"' and password = '"+ password+"'";
		System.out.println(sql);
		ResultSet rs =  stmt.executeQuery(sql);
		return rs.next();
		
	}
	
	
	public boolean login2(String username,String password) throws ClassNotFoundException, SQLException {
		//1得到Connection
		//2得到Statement
		//3得到ResultSet
		//4rs.next()返回的是什么 我们就返回啥
		String driverClassName = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/mydb3";
		String mysqlUsername = "root";
		String mysqlPassword = "123";
		Class.forName(driverClassName);
		Connection con = DriverManager.getConnection(url,mysqlUsername,mysqlPassword);
//		Statement stmt =  con.createStatement();
//		String sql = "select * from t_user where username='"+username+"' and password = '"+ password+"'";
//		System.out.println(sql);
//		ResultSet rs =  stmt.executeQuery(sql);
//		return rs.next();
	/
		/*
		 * 得到PreparedStatement
		 * 1.给出sql模版 :所有的参数用?来替代
		 * 2调用Connection方法 得到PreparedStatement
		 * 3.调用PreparedStatement的getXxx()方法给?赋值
		 * 4.调用pstmt的executeUpdate或 Query
		 */
		String sql = "select * from t_user where username=? and password=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, username); //给第一个问号赋值 值为username;
		pstmt.setString(2, password); //第二个问号赋值
		ResultSet rs = pstmt.executeQuery();
		return rs.next();
				
	}
	
	@Test
	public void fun1() throws ClassNotFoundException, SQLException{
		/*
		 * sql 攻击
		 * 
		 */
		String username = "a' or 'a'='a";
		String password = "a' or 'a'='a";
		boolean bool = login(username, password);
		System.out.println(bool);
		
		//login("zhangSan", "123");
	}
	
	@Test
	public void fun2() throws ClassNotFoundException, SQLException{
		String username = "zhangSan";
		String password = "123";
		boolean bool = login2(username, password);
		System.out.println(bool);
	}
	
	@Test
	public void fun3() throws SQLException{
		Connection con = JdbcUtils.getConnection();
		System.out.println(con);
	}
}

创建一个1.0版本的jdbc工具类

package it.cast.demo;

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

public class JdbcUtils {
	private static Properties props = null;
	// 只在JdbcUtils类被加载时执行一次
	static {
		// 给pros初始化 即加载dbconfig.properties文件到pros
		try {
			InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbconfig.properties");
			props = new Properties();
			props.load(in);
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
		// 加载驱动类
		try {
			Class.forName(props.getProperty("driverClassName"));
		} catch (ClassNotFoundException e) {
			throw new RuntimeException(e);
		}
	}

	//获取连接
	public static Connection getConnection() throws SQLException {
		/*
		 * 1.加载配置文件 2.加载驱动类 3/调用DriverManager.getConnection()
		 * 
		 */
		// InputStream in =
		// JdbcUtils.class.getClassLoader().getResourceAsStream("dbconfig.properties");
		// Properties props = new Properties();
		// props.load(in);

		return DriverManager.getConnection(props.getProperty("url"), props.getProperty("username"),
				props.getProperty("password"));

	}

}

往mysql存取文件

package it.cast.demo;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.rowset.serial.SerialBlob;

import org.apache.commons.io.IOUtils;
import org.junit.Test;

import sun.nio.ch.IOUtil;


/**
 * 大数据
 * @author 10958
 *
 */
public class Demo4 {
	/**
	 * 把mp3保存到数据库
	 * @throws SQLException 
	 * @throws IOException 
	 * @throws FileNotFoundException 
	 */
	@Test
	public void fun1() throws SQLException, FileNotFoundException, IOException{
		Connection con = JdbcUtils.getConnection();
		String sql = "INSERT INTO tab_bin values(?,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, 2);
		pstmt.setString(2, "星空.MP3");
		/*
		 * 需要得到Blob
		 * 把文件转化成byte[] 
		 * 在创建BLob
		 */
		byte[] bytes = IOUtils.toByteArray(new FileInputStream("D:/星空.mp3"));
		Blob blob = new SerialBlob(bytes);
		
		pstmt.setBlob(3, blob);
		pstmt.executeUpdate();
	}
	
	//读取MP3
	@Test
	public void fun2() throws SQLException, IOException{
		Connection con = JdbcUtils.getConnection();
		String sql = "select * from tab_bin";
		PreparedStatement pstmt = con.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		//获取名为data的列数据
		if (rs.next()) {
			Blob blob = rs.getBlob("data");
			/*
			 * 把Blob变成硬盘上的文件
			 * 1.通过Blob得到输入流对象
			 * 2.自己创建输出流对象
			 * 3.把输入流的数据写入到输出流中
			 */
			InputStream in = blob.getBinaryStream();
			OutputStream out = new FileOutputStream("D:/xingkong.mp3");
			IOUtils.copy(in, out);
		}
		
	}
}

批处理首先需要在url后面加上rewriteBatchedStatements=true

例如:String url = "jdbc:mysql://localhost:3306/exam?reewriteBatchedStatements=true"

Jdbc 入门