java连接mysql增删改查（01单增删改查）_java连接mysq 增删改查

0.创建数据库

// 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());

1.增加

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
 
public class JdbcDemo {
 
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		
		//1.加载数据库驱动
		Class.forName("com.mysql.jdbc.Driver");
		//2.创建数据库连接
		String url="jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
		String user="root";
		String password="root";
		Connection connection = DriverManager.getConnection(url, user, password);
		//3.创建statement对象
		Statement statement = connection.createStatement();
		//4.准备sql语句
		String sql="INSERT INTO user(user_name,price,create_time) VALUES(\"李雷\",200.2,'2021-12-12')";
        //5.执行sql
		int row = statement.executeUpdate(sql);
		System.out.println("row" + row);
        //6.关闭连接
        connection.close();
        
		
		
	}
 
}

2.删除

public class JdbcDemo3 {
	
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		//1.加载数据库驱动
		Class.forName("com.mysql.jdbc.Driver");
		//2.创建数据库连接
		String url="jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
		String user="root";
		String password="root";
		Connection connection = DriverManager.getConnection(url, user, password);
		//3.创建statement对象
		Statement statement = connection.createStatement();
		//4.准备sql
		String sql="DELETE FROM user WHERE user_id=1000";
		//5.将sql语句发送到MYSQL服务器
		int row = statement.executeUpdate(sql);
		System.out.println("row" + row);
        //6.关闭连接
        connection.close();
        
		
	}
}

3.修改

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
 
public class JdbcDemo2 {
 
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		// TODO Auto-generated method stub
 
		//1.加载数据库驱动
		Class.forName("com.mysql.jdbc.Driver");
		//2.创建数据库连接
		String url="jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
		String user="root";
		String password="root";
		Connection connection = DriverManager.getConnection(url, user, password);
		//3.创建statement对象
		Statement statement = connection.createStatement();
		//4.准备sql
		String sql="UPDATE user SET user_name='admin1',price=12.12,create_time=now()" + 
				"WHERE user_id=1001";
		//5.将sql语句发送到MYSQL服务器
		int row = statement.executeUpdate(sql);
		System.out.println("row:" + row);
        //6.关闭连接
        connection.close();
        
		
	}
 
}

4.查询单个

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 JdbcDemo4 {
 
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		
		//1.加载数据库驱动
		Class.forName("com.mysql.jdbc.Driver");
		//2.创建数据库连接
		String url="jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
		String user="root";
		String password="root";
		Connection connection = DriverManager.getConnection(url, user, password);
		//3.创建statement对象
		Statement statement = connection.createStatement();
		//4.准备sql
		String sql="SELECT * FROM user where user_id = 10001";
		//5.将sql语句发送到MYSQL服务器
		ResultSet rs = statement.executeQuery(sql);
		//6.处理结果集
		if(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);
		}else{
            System.out.println("结果集无数据");
        }
        //7.关闭连接
        connection.close();
		
		rs.close();
		
	}
 
}

5.查询结果集

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 JdbcDemo4 {
 
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		
		//1.加载数据库驱动
		Class.forName("com.mysql.jdbc.Driver");
		//2.创建数据库连接
		String url="jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
		String user="root";
		String password="root";
		Connection connection = DriverManager.getConnection(url, user, password);
		//3.创建statement对象
		Statement statement = connection.createStatement();
		//4.准备sql
		String sql="SELECT * FROM user";
		//5.将sql语句发送到MYSQL服务器
		ResultSet rs = statement.executeQuery(sql);
		//6.处理结果集
		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);
		}
        //7.关闭连接
        connection.close();
		
		rs.close();
		
	}
 
}