数据库 JDBC --- 在IDEA里使用数据库

案例

public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //1.    加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.    获得连接
            String username = "root";
            String password = "123456";
            String url = "jdbc:mysql://localhost:3306/cxx?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, username, password);

            //3.    定义SQL，创建状态通道（进行SQL语句的发送）
            statement = connection.createStatement();
            int result = statement.executeUpdate("insert into users(userid,uname) values(8,'cxx')");
            //      statement.executeUpdate   执行 增删改 使用 ， 返回受影响行数
            if(result > 0){
                System.out.println("执行成功！");
            }else{
                System.out.println("执行失败！");
            }
            statement.executeUpdate("update users set gradeid=1");
            statement.executeUpdate("delete from users where uname='cxx'");

            resultSet = statement.executeQuery("select * from users");//执行查询

            //4.    取出结果集数据
            while (resultSet.next()){  // 判断是否有下一条
                //取出数据
                //resultSet.getXXX("列名");
                System.out.println("姓名："+resultSet.getString("uname")+"\t年级："+resultSet.getInt("gradeid"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.    关闭资源
            try {
                if(resultSet != null){
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

MYSQL8         com.mysql.cj.jdbc.Driver         

                        jdbc:mysql://hostname:3306/databaseName?serverTimezone=UTC

MySQL            com.mysql.jdbc.Driver

                        jdbc:mysql://hostname:3306/databaseName

ORACLE         oracle.jdbc.driver.OracleDriver

                        jdbc:oracle:thin:@hostname:port Number：databaseName

DB2                 COM.ibm.db2.jdbc.net.DB2Driver

                        jdbc:db2:hostname:port Number / databaseName

SYBASE         com.sybase.jdbc.SybDriver

                        jdbc:sybase:Tds:hostname:port Number / databaseName

SQL注入

String password = " '' or 1=1";

解决方法：预状态通道     PreparedStatement

使用预状态通道  以字符串的方式处理值

案例

public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement pps = null;
        ResultSet resultSet = null;
        try {
            //1.    加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.    获得连接
            String username = "root";
            String password = "123456";
            String url = "jdbc:mysql://localhost:3306/cxx?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, username, password);

            //3.    定义SQL，创建状态通道（进行SQL语句的发送）
            String sql = "select * from users where uname=? and password=?"; // ？ 是占位符
            pps = connection.prepareStatement(sql);
            connection.createStatement();
            //给占位符赋值   (下标，数值)
            String uname = "小明";
            String pass = " '' or 1=1";
            pps.setString(1,uname);   // 从1开始
            pps.setString(2,pass);
            //执行SQL
            resultSet = pps.executeQuery();//无参

            if(resultSet.next()){   // 不能用 != null
                System.out.println("登录成功！");
            }else{
                System.out.println("登陆失败！");
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.    关闭资源
            try {
                if(resultSet != null){
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

ResultSet

 resultSet.getXXX(....)  取数据

resultSet.next( )  判断是否为空，并且指针下移

案例----多对多   学生---科目

 public Student findById(int id) {
        Connection connection = null;
        PreparedStatement pps = null;
        ResultSet resultSet = null;
        try {
            //1.    加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.    获得连接
            String username = "root";
            String password = "123456";
            String url = "jdbc:mysql://localhost:3306/cxx?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, username, password);

            //3.    定义SQL，创建状态通道（进行SQL语句的发送）
            String sql = "select * from student s,subject su,middle m where s.stuid=m.stuid and su.subid=m.subid and s.stuid=?"; // ？ 是占位符
            pps = connection.prepareStatement(sql);
            pps.setInt(1,id);
            connection.createStatement();
            //给占位符赋值   (下标，数值)
            //执行SQL
            resultSet = pps.executeQuery();//无参
            //取值

            Student student = new Student();
            List<Subject> subjectList = new ArrayList<>();

            while (resultSet.next()) {
                //1 取出各自的信息,给对象赋值
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuName(resultSet.getString("stuname"));

                Subject subject = new Subject();
                subject.setSubId(resultSet.getInt("subid"));
                subject.setSubName(resultSet.getString("subname"));
                //2.建立学生和科目的关系
                subjectList.add(subject);
            }
            student.setSubjectList(subjectList);
            return student;

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.    关闭资源
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

要找某个student对象对应的所有科目，则应该在Student中定义Subject集合对应成员。

自动提交

SQL语句中间可能夹杂着其他业务， 用事务的原子性可以确保数据安全。 

取消自动提交        connection.setAutoCommit ( false )

提交                      connection.commit ( )

回滚                      connection.rollback ( )

Savapoints 接口

Savepoint point = connection.setSavepoint ( "abc" )

......

connection.rollback ( abc );

即使发生异常，保存点之前的操作也会生效（部分提交）

批处理

1.状态通道

String string1 = " ....." , string2 = ".....";

statement.addBatch(string1);

statement.addBatch(string2);

int [ ]  ints = statement.excuteBatch;      返回受影响的行数

2.预状态通道

pps.setInt(1,李四);

pps.addBatch ( ) ;

pps.setInt(1,王五);

pps.addBatch ( ) ;

int [ ]  ints = pps.excuteBatch;