Java JDBC用法

前言

Eclipse 版本：
Eclipse IDE for Java Developers (includes Incubating components)
Version: 2021-09 (4.21.0)
Build id: 20210910-1417
MySQL版本：8.0.16

一、环境准备

1.从官网下载MySQL驱动包
2.Eclipse新建一个java项目
3.右键单击项目，在菜单中选择“Properties”选项
4.在打开的对话框中，选择“Java Build Path”选项卡。
5.点击Add External JARs…选择下载的驱动包，然后点击Apply and Close完成数据库驱动包的导入

二、使用步骤

以下代码以用户表为例，包含了JDBC对数据库增删改查所有操作，可直接复制使用

import java.sql.*;
public class SqlJdbc {
	private static String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
	private static String DB_URL = "jdbc:mysql://localhost:3306/new_schema?		useUnicode=true&characterEncoding=UTF-8";
	private static String USER = "root";
	private static String PASS = "123456";
	
	/**
	* 获取一个数据库链接
	*/
	public static Connection getConnection() throws SQLException{
		try {
		
			//加载数据库链接驱动
			Class.forName(JDBC_DRIVER);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return DriverManager.getConnection(DB_URL, USER, PASS);
	}
	
	//查询所有用户、
	public static void selectuser() throws SQLException {
		String sql = "select * from user_table";
		Connection conn = getConnection(); // 获取连接，返回一个Connection对象
		conn.setAutoCommit(false); // 设置事务手动提交，后面的commit
		// 执行sql语句
		PreparedStatement pst = conn.prepareStatement(sql);
		// 执行查询语句并返回结果集
		ResultSet rs = pst.executeQuery();
		
		while (rs.next()) {
			// 通过字段检索
			int user_id = rs.getInt("user_id");
			String user_name = rs.getString("user_name");
			String user_password = rs.getString("user_password");
			System.out.print("user_id: " + user_id + "\n");
			System.out.print("user_name: " + user_name + "\n");
			System.out.print("user_password: " + user_password + "\n");
			System.out.print("\n");
		}
		conn.commit();
		conn.close();
	}
	
	//登陆验证
	public static boolean login(String na, String pwd) throws SQLException{
		boolean a = false;
		String sql = "select * from user_table where user_name = '"+ na +"' and user_password = '"+ pwd +"'";
		Connection conn = getConnection();	//获取连接，返回一个Connection对象
		conn.setAutoCommit(false);		//设置事务手动提交，后面的commit
		//执行sql语句
		PreparedStatement pst = conn.prepareStatement(sql);
		//执行查询语句并返回结果集
		ResultSet rs = pst.executeQuery();
		if (rs.next()) {
			//注意：这里要与数据库里的字段对应
			a =  true;
		}
		conn.commit();
		conn.close();
		return a;
	}
	
	//注册用户
	public static void add(String na, String pwd) throws SQLException{
		String sql = "INSERT INTO user_table (user_name,user_password) 	VALUES('"+na+"','"+pwd+"')";
		Connection conn = getConnection();
		conn.setAutoCommit(false);
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		preparedStatement.executeUpdate();
		conn.commit();
		conn.close();
	}

	//修改密码
	public static void updata(String na, String pwd) throws SQLException{
		String sql = "update user_table set user_password = '"+pwd+"' where user_name = '"+ na +"'";
		Connection conn = getConnection();
		conn.setAutoCommit(false);
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		preparedStatement.executeUpdate();
		conn.commit();
		conn.close();
	}

	//删除用户
	public static void delete(String na) throws SQLException{
		String sql = "DELETE FROM user_table where user_name = '"+na+"'";
		Connection conn = getConnection();
		conn.setAutoCommit(false);
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		preparedStatement.executeUpdate();
		conn.commit();
		conn.close();
	}
}