MYSQL （JDBC）操作（一）

package mysqljdbc;

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

public class ConnectionUtil {
	private static String URL="jdbc:mysql://localhost:3307/scott"+"?useSSL=false&useUnicode=true"+"&characterEncoding=UTF-8&serverTimezone=UTC";
//	private static String URL = "jdbc:mysql://localhost:3307/scott" +"?useUnicode=true&useJDBCCompliantTimezoneShift=true"+"&useLegacyDatetimeCode=false&serverTimezone=UTC";
	private static String DRIVER="com.mysql.cj.jdbc.Driver";
	private static String USER = "biubiu";
	private static String PASSWORD = "123456";
	//加载驱动
	static{
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 取得连接对象
	 * @return
	 */
	public static Connection getConnection(){
		try {
			return DriverManager.getConnection(URL,USER,PASSWORD);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	public static void close(Connection conn){
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

package mysqljdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
/*
 * 插入数据
 */

public class MysqlTest {
	public static void main(String[] args) throws Exception {
//		if(insertEmp()){
//			System.out.println("插入数据成功");
//		}else {
//			System.out.println("插入数据失败");
//		}
		System.out.println(updateDeposit(200));
	}
	public static boolean updateDeposit(double num) throws Exception{
		//是否成功的标记
		boolean flag = true;
		//取得数据库连接
		Connection conn = ConnectionUtil.getConnection();
		//SQL语句
		String sql1 = "UPDATE emp set deposit = deposit-"+num+"where ename = 'jack'";
		String sql2 = "UPDATE emp set deposit = deposit+"+num+"where ename = 'mike'";
		//转换数据库指令
		PreparedStatement pst1=conn.prepareStatement(sql1);
		PreparedStatement pst2=conn.prepareStatement(sql2);
		//取消自动提交。如果不设置，默认自动提交
		conn.setAutoCommit(false);
		//执行费语句
		try {
			pst1.executeUpdate();
			pst2.executeUpdate();
			//如果没异常，则手动提交
			conn.commit();
		} catch (Exception e) {
			flag = false;
			e.printStackTrace();
			//如果出现异常，回滚
			conn.rollback();
			// TODO: handle exception
		}finally{
			//关闭资源
			ConnectionUtil.close(conn);
		}
		
		return flag;
	}
	public static boolean insertEmp() throws Exception{
		//取得连接对象
		Connection conn = ConnectionUtil.getConnection();
		//定义语句
		String sql = "INSERT INTO emp(empno,ename,job,sal) VALUES(1002,'mike','salesman',3500)";
		//取得发送sql语句的对象
		PreparedStatement pst=conn.prepareStatement(sql);
		//执行语句
		int row = pst.executeUpdate();
		//关闭资源
		ConnectionUtil.close(conn);
		return row>0;
	}

}

在数据更新的时候可能会要考虑，事务的原子性，一个事物多个操作要么都成功要么都失败
将默认自动提交取消，改为手动提交（若出现异常就回滚，否则提交）