JDBC操作步骤和JDBC事务

1、加载驱动

这是1.8版本包里面的，如果是1.5的话可以把cj除去

//加载驱动 
Class.forName("com.mysql.cj.jdbc.Driver");

2、获得连接

 //2、打开连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306", "root", "123456");

3、编写sql

  //3. 预处理sql语句
        PreparedStatement preparedStatement = connection.prepareStatement("insert into hero(username,sex,age,type,price,aag) values(?,?,?,?,?,?)");

4、获得执行sql语句的对象

//4. 获得执行sql语句的对象

        preparedStatement.setString(1,"金蝉");
        preparedStatement.setString(2,"男");
        preparedStatement.setString(3,"11");
        preparedStatement.setString(4,"法师");
        preparedStatement.setString(5,"18888");
        preparedStatement.setString(6,"100");

5、执行sql语句，并返回结果

//5. 执行sql语句，并返回结果
        preparedStatement.execute();

//基于查询语句
ResultSet resultSet = preparedStatement.executeQuery();

 

6、处理结果

（这个是基于第五步的查询语句）

//6. 处理结果
        while (resultSet.next()){
            System.out.println(resultSet.getString(1));
        }

7、释放资源

   //7. 释放资源
        preparedStatement.close();
        connection.close();

jdbc事务

1.只要中途出现错乱都两边都要回滚回去，不会执行下一步

 /**
     * 模拟转账
     * @param args
     */
    public static void main(String[] args)  {
        Connection connection=null;
        try {
            //1、加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2、打开连接
           connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/game", "root", "123456");
           //启动手动提交
            connection.setAutoCommit(false);
            //3、预处理mysql
            PreparedStatement preparedStatement = connection.prepareStatement("update a set  money = money - ? where id = ?");
            preparedStatement.setDouble(1,500);
            preparedStatement.setInt(2,1);
            int num = preparedStatement.executeUpdate();
            System.out.println(num);

            System.out.println(1/0);

            preparedStatement.setDouble(1,-500);
            preparedStatement.setInt(2,2);
            num = preparedStatement.executeUpdate();
            System.out.println(num);

            //提交事务
            connection.commit();
        } catch (Exception e) {
            try {
                //事务回滚
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

1.数据库操作失败数据

1.数据库操作成功后的数据

  

 JDBC示例：

要创建DBUtils工具类

package com.blb.day01;

import java.sql.*;

public class DBUtils {
   static {
       try {
           Class.forName("com.mysql.cj.jdbc.Driver");
       } catch (ClassNotFoundException e) {
           e.printStackTrace();
       }
   }

    /**
     * 获取连接
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/game","root","123456");
    }

    /**
     * 执行增删改查
     * @param connection
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static int executeUpdate(Connection connection, String sql, Object... params) throws SQLException {
        return setParams(connection,sql,params).executeUpdate();
    }

    /**
     * 设置参数
     * @param connection
     * @param sql
     * @param params
     * @return
     */
    private static PreparedStatement setParams(Connection connection, String sql, Object[] params) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i <params.length ; i++) {
            preparedStatement.setObject(i+1,params[i]);
        }
        return preparedStatement;
    }

    /**
     * 执行查询
     * @param connection
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static ResultSet executeQuery(Connection connection, String sql, Object... params) throws SQLException {
        return setParams(connection,sql,params).executeQuery();
    }
}

测试类： 

package com.blb.day01;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Scanner;

public class Demo1 {
static Scanner scanner = new Scanner(System.in);

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        while (true){
            String[] strings ="录入员工信息,删除员工信息,修改员工信息,员工打卡,查看全部员工打卡信息,查看指定员工全部打卡信息,查看指定员工指定日期打卡信息,退出".split(",");
            for (int i = 0; i <strings.length ; i++) {
                System.out.println(i+","+strings[i]);
            }
            System.out.println("请输入：");
            int num = scanner.nextInt();
            switch (num) {
                case 0:
                    addEmp();
                    break;
                case 1:
                    delEmp();
                    break;
                case 2:
                    updateEmp();
                    break;
                case 3:
                    getSignInfo();
                    break;
                case 4:
                    System.exit(0);
                    break;
                default:
                    System.out.println("输入有误请重新输入");
                    break;
            }
        }
    }

    /**
     * 修改
     */
    private static void updateEmp() throws SQLException, ClassNotFoundException {
        System.out.println("请输入要打卡的员工编号：");
        int no = scanner.nextInt();
        System.out.println("请输入新的姓名：");
        String name = scanner.next();
        int i = DBUtils.executeUpdate(DBUtils.getConnection(), "update emp set ename=? where empno = ?", name, no);
        if (i>0){
            System.out.println("修改成功！");
        }else {
            System.out.println("修改失败！");
        }
    }

    /**
     * 员工打卡
     */
    private static void getSignInfo() throws SQLException, ClassNotFoundException {
        while (true){
            System.out.println("请输入要打卡的员工编号：");
            int num = scanner.nextInt();
            ResultSet resultSet = DBUtils.executeQuery(DBUtils.getConnection(), "select * from emp where empno=?", num);
            if (resultSet.next()){
                String date = LocalDate.now().toString();
                ResultSet resultSet1 = DBUtils.executeQuery(DBUtils.getConnection(), "select * from signinfo where empno=? and signdate = ?", num, date);
                 String datetime = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss").format(LocalDateTime.now());
                if (resultSet1.next()){
                    DBUtils.executeUpdate(DBUtils.getConnection(),"update signinfo set signout = ? where empno = ?",datetime,num);
                    System.out.println("下班打卡成功！");
                }else {
                    DBUtils.executeUpdate(DBUtils.getConnection(),"insert into signinfo(empno, signdate, signin) values (?,?,?)",num,LocalDate.now(),datetime);
                    System.out.println("上班打卡成功！");
                }
                break;
            }else {
                System.out.println("员工不存在！！");
            }
        }
    }

    /**
     * 删除
     */
    private static void delEmp() throws SQLException, ClassNotFoundException {
        System.out.println("请输入要删除的员工编号：");
        int num = scanner.nextInt();
        int i = DBUtils.executeUpdate(DBUtils.getConnection(), "delete from emp where empno=?", num);
        if (i>0){
            System.out.println("删除失败");
        }else {
            System.out.println("删除成功！");
        }
    }

    /**
     * 新增
     */
    private static void addEmp() throws SQLException, ClassNotFoundException {
        System.out.println("请输入要添加员工姓名：");
        String name = scanner.next();
        DBUtils.executeUpdate(DBUtils.getConnection(),"insert into emp(ename) values(?)",name);
        System.out.println("增加成功！");

    }
}