Java连接MySql数据库实现增删改查功能

一不用工具类，简单实现连接数据库并查询package com.liu;import java.sql.*;public class JDBCTest {public static void main(String[] args) throws Exception {//1. 加载驱动Class.forName("com.mysql.cj.jdbc.Driver");//固定写法//2. 用户信息

天行九歌。

7725人浏览 · 2022-03-10 21:32:35

天行九歌。 · 2022-03-10 21:32:35 发布

一不用工具类，简单实现连接数据库并查询

package com.liu;

import java.sql.*;

public class JDBCTest {
	public static void main(String[] args) throws Exception {
		//1. 加载驱动
		Class.forName("com.mysql.cj.jdbc.Driver");//固定写法
		//2. 用户信息和url
		//useUnicode=true&characterEncoding=utf8&&useSSL=true
		String url ="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&&useSSL=false";
		String name = "root";
		String password = "123456";

		//3. 连接成功，返回数据库对象  connection代表数据库
		Connection connection= DriverManager.getConnection(url,name,password);
		//4. 执行SQL的对象 statement 执行SQL的对象
		Statement statement = connection.createStatement();

		//5. 执行SQL的对象 去执行SQL   可能存在结果，查看返回结果
		String sql="SELECT * FROM user";
		ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部查询的结果
		while(resultSet.next()){
			System.out.println("id "+resultSet.getObject("id"));
			System.out.println("name "+resultSet.getObject("name"));
			System.out.println("password "+resultSet.getObject("password"));
			System.out.println("phone "+resultSet.getObject("phone"));
		}
		//6. 释放连接
		resultSet.close();
		statement.close();
		connection.close();
	}
}

二利用JAVA反射特性借用工具类实现连接数据库

1，用statement对象解析sql命令，不能够防止sql注入。

dp.properutils

driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&&useSSL=false
username = root
password = 123456

jdbcUtils.class

package com.liu;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class jdbcUtils {
	private static String driver = null;
	private static String url = null;
	private static String username = null;
	private static String password = null;
	static {
		try{
			InputStream in = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
			Properties properties = new Properties();
			properties.load(in);
			driver = properties.getProperty("driver");
			url = properties.getProperty("url");
			username = properties.getProperty("username");
			password = properties.getProperty("password");

			Class.forName(driver);
		} catch (IOException | ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, username, password);
	}

	public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
		if(resultSet != null){
			resultSet.close();
		}
		if(connection != null){
			connection.close();
		}
		if(statement != null){
			statement.close();
		}
	}
}

增加数据功能insertTest

package com.liu;

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

public class insertTest {
	public static void main(String[] args) throws SQLException {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = jdbcUtils.getConnection();
			statement = connection.createStatement();
			String sql = "INSERT INTO USER(`id`,`name`,`phone`,`password`)" +
					"VALUES('4','刘六','18715211308','123456')";
			int i = statement.executeUpdate(sql);
			if(i > 0){
				System.out.println("插入成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			jdbcUtils.release(connection, statement, resultSet);
		}
	}
}

删除数据功能deleteTest

package com.liu;

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

public class deleteTest {
	public static void main(String[] args) throws SQLException {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = jdbcUtils.getConnection();
			statement = connection.createStatement();
			String sql = " DELETE FROM user WHERE id = '4' ";
			int i = statement.executeUpdate(sql);
			if(i > 0){
				System.out.println("删除成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			jdbcUtils.release(connection, statement, resultSet);
		}
	}
}

改变数据功能updateTest

package com.liu;

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

public class updateTest {
	public static void main(String[] args) throws SQLException {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
		 	connection = jdbcUtils.getConnection();
			statement = connection.createStatement();
			String sql = "UPDATE user SET name = '王八蛋' WHERE id = '3'";
			int i = statement.executeUpdate(sql);
			if(i > 0){
				System.out.println("更新成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			jdbcUtils.release(connection, statement, resultSet);
		}
	}
}

查询数据功能selectTest

package com.liu;

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

public class selectTest {
	public static void main(String[] args) throws SQLException {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = jdbcUtils.getConnection();
			statement = connection.createStatement();

			String sql = "SELECT * from user";
			resultSet = statement.executeQuery(sql);
			while(resultSet.next()){
				System.out.println(resultSet.getObject("id"));
				System.out.println(resultSet.getObject("name"));
				System.out.println(resultSet.getObject("phone"));
				System.out.println(resultSet.getObject("password"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			jdbcUtils.release(connection, statement, resultSet);
		}
	}
}

2，用PreparedStatement对象解析sql，可以防止sql注入问题

package com.liu;
import com.liu.jdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class test {
	public static void main(String[] args) {
		Connection connection= null;
		PreparedStatement preparedStatement = null;
		try {
			connection = jdbcUtils.getConnection();
			//区别
			//使用问好占位符代替参数
			String sql = "insert into users(`id`,`name`,`phone`,`password`) values(?,?,?,?,?)";
			preparedStatement = connection.prepareStatement(sql);//预编译sql，先写sql然后不执行
			//手动赋值
			preparedStatement.setObject(1,"4");
			preparedStatement.setObject(2,"SANJIN");
			preparedStatement.setObject(3,"13220677880");
			preparedStatement.setObject(4,"123456");
			//执行
			int i = preparedStatement.executeUpdate();
			if (i>0){
				System.out.println("插入成功");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				jdbcUtils.release(connection,preparedStatement,null);
			} catch (SQLException throwables) {
				throwables.printStackTrace();
			}
		}
	}
}