java连接mysql增删改查（02通过封装公共类解决代码冗余）

00.创建数据库

// 1)、创建数据库
CREATE DATABASE jdbc DEFAULT CHARACTER SET UTF8;
// 2)、切换数据库
USE jdbc;
// 3)、创建数据库表
CREATE TABLE user(
`user_id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`user_name` VARCHAR(20) NOT NULL COMMENT '用户名',
`price` double(10,2) DEFAULT 0.0 COMMENT '价格',
`create_time` DATETIME DEFAULT NULL COMMENT '创建时间'
)ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=UTF8 COMMENT="用户表";
// 4)、插入用户
INSERT INTO user(user_name,price,create_time)VALUES('admin',100.12,now());

01.创建工具类JdbcUtil.java

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

/*
 * 线程不安全
 * */
public class JdbcUtil {
	private static final String JDBC_URL="jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
	private static final String USER_NAME="root";
	private static final String PASSWORD="root";

	public static Connection getConnection() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager.getConnection(JDBC_URL, USER_NAME, PASSWORD);
		return connection;
		
	}
	public static void close(Connection connection)  {
		
		if(connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

02.CURD

package com.detian;

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

public class JdbcDemo5 {

	
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		add();
		deleteById();
		update();
		selectById();
		selectAll();
		
	}
	public static void add() throws ClassNotFoundException, SQLException {
		//1.获取连接
		Connection connection =JdbcUtil.getConnection();
		//2.创建statement对象
		Statement statement = connection.createStatement();
		//3.准备sql
		String sql="INSERT INTO user(user_name,price,create_time) VALUES(\"李雷111\",200.2,now())";
		//4.将sql语句发送到MYSQL服务器
		int row = statement.executeUpdate(sql);
		System.out.println("row" + row);
		//5.关闭连接
		JdbcUtil.close(connection);
		
	}
	public static void deleteById() throws ClassNotFoundException, SQLException {
		//1.获取连接
		Connection connection =JdbcUtil.getConnection();
		//2.创建statement对象
		Statement statement = connection.createStatement();
		//3.准备sql
		String sql="DELETE FROM user WHERE user_id=1000";
		//4.将sql语句发送到MYSQL服务器
		int row = statement.executeUpdate(sql);
		System.out.println("row" + row);
		//5.关闭连接
		JdbcUtil.close(connection);
		
	}
	public static void update() throws ClassNotFoundException, SQLException {
		//1.获取连接
		Connection connection =JdbcUtil.getConnection();
		//2.创建statement对象
		Statement statement = connection.createStatement();
		//3.准备sql
		String sql="UPDATE user SET user_name='admin1',price=12.12,create_time=now()" + 
				"WHERE user_id=1001";
		//4.将sql语句发送到MYSQL服务器
		int row = statement.executeUpdate(sql);
		System.out.println("row" + row);
		
	}
	public static void selectById() throws ClassNotFoundException, SQLException {
		//1.获取连接
		Connection connection =JdbcUtil.getConnection();
		//2.创建statement对象
		Statement statement = connection.createStatement();
		//3.准备sql
		String sql="SELECT * FROM user where user_id = 1001";
		//4.将sql语句发送到MYSQL服务器
		ResultSet rs = statement.executeQuery(sql);
		//5.处理结果集
		while(rs.next()) {
			//处理结果
			System.out.println("结果集有数据");
			int userId = rs.getInt("user_id");
			String username = rs.getString("user_name");
			double price =rs.getDouble("price");
			Date date = rs.getDate("create_time");
			System.out.println("userId:"+userId);
			System.out.println("username:"+username);
			System.out.println("price:"+price);
			System.out.println("date:"+date);
		}
		//6.关闭连接
		JdbcUtil.close(connection);
		
	}
	public static void selectAll() throws ClassNotFoundException, SQLException {
		//1.获取连接
		Connection connection =JdbcUtil.getConnection();
		//2.创建statement对象
		Statement statement = connection.createStatement();
		//3.准备sql
		String sql="SELECT * FROM user ";
		//4.将sql语句发送到MYSQL服务器
		ResultSet rs = statement.executeQuery(sql);
		//5.处理结果集
		while(rs.next()) {
			//处理结果
			System.out.println("结果集有数据");
			int userId = rs.getInt("user_id");
			String username = rs.getString("user_name");
			double price =rs.getDouble("price");
			Date date = rs.getDate("create_time");
			System.out.println("userId:"+userId);
			System.out.println("username:"+username);
			System.out.println("price:"+price);
			System.out.println("date:"+date);
		}
		//6.关闭连接
		JdbcUtil.close(connection);
		
	}

}