java连接mysql增删改查（04修改jdbcutil工具类将配置信息外置）

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.在src目录下创建配置文件db.properties

jdbc_url=jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8
jdbc_user=root
jdbc_passwd=root

02.创建工具类JdbcUtil3.java

package com.detian;

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 JdbcUtil3 {
	private static  String JDBC_URL=null;
	private static  String USER_NAME=null;
	private static  String PASSWORD=null;

	//装我们的连接
	private static ThreadLocal<Connection> pool = new ThreadLocal<Connection>();
	
	//静态代码块
	static {
		//加载外部属性资源文件
		Properties p = new Properties();
		//类加载器
		InputStream in = JdbcUtil3.class.getClassLoader().getSystemResourceAsStream("db.properties");
		try {
			p.load(in);
			JDBC_URL = p.getProperty("jdbc_url");
			USER_NAME = p.getProperty("jdbc_user");
			PASSWORD = p.getProperty("jdbc_passwd");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	//获取连接
	public static Connection getConnection() throws ClassNotFoundException, SQLException {
		
		Connection connection = pool.get();
		if(connection==null) {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection(JDBC_URL, USER_NAME, PASSWORD);
			pool.set(connection);
		}
		return connection;
	}
	//关闭连接
	public static void close()  {
		Connection connection = pool.get();
		if(connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		//移除此线程局部变量当前线程值
		pool.remove();
	}
}

03.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 JdbcDemo7 {

	
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		add();
		deleteById();
		update();
		selectById();
		selectAll();
		
	}
	public static void add() throws ClassNotFoundException, SQLException {
		//1.获取连接
		Connection connection =JdbcUtil3.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.关闭连接
		JdbcUtil3.close();
		
	}
	public static void deleteById() throws ClassNotFoundException, SQLException {
		//1.获取连接
		Connection connection =JdbcUtil3.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.关闭连接
		JdbcUtil3.close();
		
	}
	public static void update() throws ClassNotFoundException, SQLException {
		//1.获取连接
		Connection connection =JdbcUtil3.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);
		//5.关闭连接
		JdbcUtil3.close();
		
	}
	public static void selectById() throws ClassNotFoundException, SQLException {
		//1.获取连接
		Connection connection =JdbcUtil3.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.关闭连接
		JdbcUtil3.close();
		
	}
	public static void selectAll() throws ClassNotFoundException, SQLException {
		//1.获取连接
		Connection connection =JdbcUtil3.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.关闭连接
		JdbcUtil3.close();
		
	}

}