java连接mysql增删改查（05PreparedStatement操作sql语句）

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.PreparedStatement操作sql语句

package com.detian;

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

/**
 *  使用PreoaredStatement进行sql操作
 * 
 * @author 63516
 *
 */
public class JdbcDemo8 {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		
		Connection connection =JdbcUtil3.getConnection();
		//?：占位符，英文半角符号
		String sql="INSERT INTO user(user_name,price,create_time) VALUES(?,?,?)";
		
		/**
		 * 预编译的作用，读取sql语句中有几个占位符符号，以便我们给sql赋值
		 */
		PreparedStatement ps = connection.prepareStatement(sql);//预编译
		ps.setString(1, "laowang");
		ps.setDouble(2, 1000);
		ps.setDate(3, new Date(new java.util.Date().getTime()));
		
		int row = ps.executeUpdate();
		System.out.println("row:" + row);
		//关闭连接
		JdbcUtil3.close();
		
		
	}

}