JDBC

目录

• JDBC
  • JDBC访问数据库步骤
  • 模拟登录
  • 事务

JDBC

JDBC访问数据库步骤

• 1：加载一个Driver驱动

• 2：创建数据库连接（Connection）

• 3 ：创建SQL命令发送器Statement

• 4：通过Statement发送SQL命令并得到结果

• 5：处理结果（select语句）

• 6：关闭数据库资源

• ResultSet

• Statement

• Connection

public class TestInsert {
    public static void main(String[] args) throws Exception{
        String driver = "com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql://127.0.0.1:3306/shiyan?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        String user="root";
        String pasaword="tcx119";
        //加载驱动
        Class.forName (driver);
        //和数据库建立连接
        Connection conn = DriverManager.getConnection (url,user,pasaword);
       // System.out.println (conn);
        //创建sql命令发射器
        Statement smt = conn.createStatement ();
        //准备sql命令
        String sql = "insert into score values(12345666,'X6666',90.2,85.3)";
        //处理结果
        int n = smt.executeUpdate (sql);
        if(n>0){
            System.out.println ("插入成功");
        }else{
            System.out.println ("插入失败");
        }
        //关闭资源
        smt.close ();
        conn.close ();
    }
}

查询的结果

code

//这是Emp的代码
public class Emp {
    private String teacherno;
    private String tname;
    private String major;
    private String prof;
    private String department;

    public Emp() {
    }

    public Emp(String teacherno, String tname, String major, String prof, String department) {
        this.teacherno = teacherno;
        this.tname = tname;
        this.major = major;
        this.prof = prof;
        this.department = department;
    }

    public String getTeacherno() {
        return teacherno;
    }

    public void setTeacherno(String teacherno) {
        this.teacherno = teacherno;
    }

    public String getTname() {
        return tname;
    }

    public void setTname(String tname) {
        this.tname = tname;
    }

    public String getMajor() {
        return major;
    }

    public void setMajor(String major) {
        this.major = major;
    }

    public String getProf() {
        return prof;
    }

    public void setProf(String prof) {
        this.prof = prof;
    }

    public String getDepartment() {
        return department;
    }

    public void setDepartment(String department) {
        this.department = department;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "teacherno='" + teacherno + '\'' +
                ", tname='" + tname + '\'' +
                ", major='" + major + '\'' +
                ", prof='" + prof + '\'' +
                ", department='" + department + '\'' +
                '}';
    }
}

//这才是主场
public class TestSelect {
    public static void main(String[] args) throws SQLException {
        List<Emp> list = selectAll ();
        for (Emp emp:list){
            System.out.println (emp.getTeacherno ()+"\t"+emp.getTname ()+
                    "\t"+emp.getMajor ()+"\t"+emp.getProf ()+"\t"+emp.getDepartment ());
        }
    }
    //后台处理
    public static List<Emp> selectAll() throws SQLException {
        String driver = "com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql://127.0.0.1:3306/shiyan?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        String user="root";
        String password="tcx119";
        Connection conn=null;
        Statement stmt = null;
        ResultSet rs=null;
        Emp emp = null;
        List<Emp> list =new ArrayList<Emp>();
        try{
            Class.forName (driver);
            conn = DriverManager.getConnection (url,user,password);
            stmt= conn.createStatement ();
            String sql = " select * from teacher";
            rs= stmt.executeQuery (sql);
            while(rs.next ()){
                String teacherno = rs.getString ("teacherno");
                String tname = rs.getString ("tname");
                String major = rs.getString ("major");
                String prof = rs.getString ("prof");
                String department = rs.getString ("department");
                emp=new Emp (teacherno,tname,major,prof,department);
                list.add (emp);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace ();
        } finally {
            assert rs != null;
            rs.close ();
            stmt.close ();
            conn.close ();
        }
        return list;
    }
}

模拟登录

public class Login {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        //前台
        Scanner sc = new Scanner (System.in);
        System.out.println ("请输入用户名");
        String userId = sc.next ();
        System.out.println ("请输入密码");
        String inputPassword = sc.next ();
        User user = testLogin (userId,inputPassword);
        if (user==null){
            System.out.println ("登录失败,请检查用户名或密码是否正确");
        }else{
            System.out.println ("登录成功： 姓名： "+user.getRealName ());
        }

    }
//    后台
    public static User testLogin(String userId,String inputPassword) throws ClassNotFoundException, SQLException {
        String driver = "com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql://127.0.0.1:3306/shiyan?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        String user="root";
        String pasaword="tcx119";
        Class.forName (driver);
        User user2=null;
        Connection conn = DriverManager.getConnection (url,user,pasaword);
        String sql = "select * from t_user where userid = ? and password = ? ";

//        Statement stmt = conn.createStatement ();
        PreparedStatement pstmt = conn.prepareStatement (sql);
        pstmt.setString (1,userId);
        pstmt.setString (2,inputPassword);
        ResultSet rs =  pstmt.executeQuery ();
        if(rs.next ()){
            String realName=rs.getString ("realname");
            double money = rs.getDouble ("money");
            user2 = new User (userId,realName,inputPassword,money);
            return user2;
        }
        return user2;
    }
}

事务

public class TestTransation {
    public static void main(String[] args)  {
        Connection conn=null;
        try{
            String driver = "com.mysql.cj.jdbc.Driver";
            String url="jdbc:mysql://127.0.0.1:3306/shiyan?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
            String user="root";
            String pasaword="tcx119";
            Class.forName (driver);
            conn = DriverManager.getConnection (url,user,pasaword);
            Statement stmt = conn.createStatement ();
            conn.setAutoCommit (false);
            stmt.executeUpdate ("update t_user set money = money-2000 where userid = 'lisi' ");
            stmt.executeUpdate ("update t_user set money = money+2000 where userid = 'zhangsan' ");
            conn.commit ();
        } catch (ClassNotFoundException e) {
            e.printStackTrace ();
        } catch (SQLException e) {
            //手动的回滚事务,回到所有DML操作执行之前的状态
            try {
                assert conn != null;
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

            e.printStackTrace ();
        }
        
    }
}