Java对MySQL数据库进行增删改查的操作(一)

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


/** 

* @author 作者 Knight: 

* @version 创建时间：2019年4月17日 上午9:09:20 

* 类说明 JDBC对MySQL数据库进行增删改查

*/
public class MainClass {
	//主入口
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		select();
	}
	//查询
	public static void select(){
		try {
			//加载驱动程序, MySQL8的驱动与MySQL5有不一样的地方
			//MySQL5的驱动:com.mysql.jdbc.Driver
			Class.forName("com.mysql.cj.jdbc.Driver");
			//建立连接,MySQL8任然与MySQL5不同,必须添加时区才可以:serverTimezone=GMT;GMT(GreenWich Mean Time) 格林尼治标准时间
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/person?serverTimezone=GMT","root","951226");
			//输出下面这条语句说明连接数据库成功
			System.out.println("数据库连接成功!");
			//定义SQL语句
			String sql = "select * from user";
			//接收SQL语句
			PreparedStatement statement = con.prepareStatement(sql);
			//执行SQL语句,并接受结果集
			ResultSet rs = statement.executeQuery();
			System.out.println("-----------------");
			System.out.println("执行结果如下所示:");  
			System.out.println("-----------------");  
			System.out.println("id" + "\t" + "姓名");  
			System.out.println("-----------------");  
			Integer id = null;
			String name = null;
			//rs.next()返回值为布尔型
			while(rs.next()){
				//获取name这列数据,传的是table的列标签name
				name = rs.getString("name");
				//获得id这列数据
				id = rs.getInt("id");
			 
				//输出结果
				System.out.println(id + "\t" + name);
			}
			rs.close();
			statement.close();
			con.close();
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void update(String name,int id){
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/person?serverTimezone=GMT","root","951226");
			System.out.println("连接数据库成功!");
			String sql = "update user set name = '" + name + "'where id = " + id + ";";
			//接收SQL语句
			PreparedStatement statement =  connection.prepareStatement(sql);
			//执行SQL语句
			statement.execute();
			statement.close();
			connection.close();
			System.out.println("id为" +  id + "的数据的name已更新为" + name);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public static void delete(int id){
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/person?serverTimezone=GMT","root","951226");
			System.out.println("连接数据库成功!");
			String sql = "delete from user where id = " + id;
			PreparedStatement statement = connection.prepareStatement(sql);
			statement.execute();
			statement.close();
			connection.close();
			System.out.println("id为" + id + "的数据已删除!");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void insert(String name){
		//MySql, Oracle, SQL Server
		try {
			//加载驱动程序
			Class.forName("com.mysql.cj.jdbc.Driver");
			//建立连接
			Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/person?serverTimezone=GMT", "root", "951226");
			System.out.println("连接数据库成功!");
			//定义SQL语句
			String sql = "insert into user values(null,'" +  name + "');";
			//接收SQL语句
			PreparedStatement statement =  connection.prepareStatement(sql);
			//执行SQL语句
			statement.execute();
			statement.close();
			connection.close();
			System.out.println("添加一条数据:name--------> " +  name + ";");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}