JDBC的相关知识

1.概念:Java DataBase Connectivity Java 数据库连接,Java语言操作数据库

JDBC本质:其实是官方(sun公司)定义的一套操作所有关系型数据库的规则,即接口。各个数据库厂商去实现这套接口,提供数据库驱动jar包。我们可以使用这套接口(JDBC)编程,真正执行的代码是驱动jar包中的实现类

2.步骤:(后面有改进部分)

        1.导入驱动jar包

        2.注册驱动

        3.获取数据库连接对象 Connection

        4.定义sql

        5.获取执行sql语句的对象 Statement

        6.执行sql,接收返回对象

        7.处理结果

        8.释放资源

实现代码:

 //1.导入驱动jar包
            //2.注册驱动
                Class.forName("com.mysql.jdbc.Driver");
            //3.获取数据库连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1","root","root");
            //4.定义sql语句
            String sql = "update account set balance = 1500 where id = 1";
            //5.获取执行sql的对象 Statement
            Statement stmt = conn.createStatement();
            //6.执行sql
            int count = stmt.executeUpdate(sql);
            //7.处理结果
            System.out.println(count);
            //8.释放资源
            stmt.close();
            conn.close();

3.详解各个对象:

        1.DriverManager:驱动管理对象

                功能:

                        1.1.注册驱动:告诉程序该使用哪一个数据库驱动jar

                                static void registerDriver(Driver driver):注册与给定的驱动程序 DriverManager

                                写代码使用:Class.forName("com.mysql.jdbc.Driver");

                                通过查看源码发现:在com.mysql.jdbc.Driver类中存在静态代码块

static{
        try{
            java.sql.DriverManager.registerDriver(new Driver());
}catch (SQLException E){
    throw new RuntimeException("Can't register driver");
}
}

注意:mysql5之后的驱动jar包可以省略注册驱动的步骤。

                        1.2.获取数据库连接:

                               方法:static Connection getConnection(String url,String user,String password)

                                参数:

                                       url:指定连接路径

                                        语法:jdbc:mysql://ip地址(域名):端口号/数据库名称

                                        细节:如果连接的是本机mysql服务器,并且mysql服务器默认端口号是3306,则url可以简写为:jdbc:mysql:///数据库名称

                                        user:用户名

                                        password:密码

                    2.Connection:数据库连接对象

                        2.1.获取执行sql的对象

                                Statement createStatement()

                                Preparedstatement   preparedstatement(String sql)

                        2.2.管理事务:

                                开启事务:setAutoCommit(boolean autoCommit):调用该方法设置参数为false,即开启事务

                                提交事务:commit()

                                回滚事务:  rollback()    

                          3.Statement : 执行sql的对象

                                        3.1.执行sql

                                                1.boolean execute(String sql) : 可以执行任意的sql

                                                2.int executeupdate(String sql) : 执行DML(insert,update,delete)语句,DDL(create,alter,drop)语句

                                                *返回值:影响的行数,可以通过这个影响的行数判断DML语句是否执行成功   返回值>0则执行成功,反之,则失败

                                                3.ResultSet executeQuery(String sql)  : 执行DQL(select)语句

练习:向数据库中添加一条信息

/**
 * jdbc练习,向数据库中添加信息
 */
public class JdbcDemo2 {
    public static void main(String[] args) {
        Connection conn =null;
        Statement stat = null;
        int num = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String sql = "insert into account value(4,1000)";

            conn = DriverManager.getConnection("jdbc:mysql:///db1", "root", "root");

            stat = conn.createStatement();

            num = stat.executeUpdate(sql);

            System.out.println(num);
            if(num>0){
                System.out.println("添加成功");
            }else{
                System.out.println("添加失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

}

 练习:查询表中的数据

/**
 * jdbc练习,查询表中的信息
 */
public class JdbcDemo3 {
    public static void main(String[] args) {
        Connection conn =null;
        Statement stat = null;
        ResultSet set = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String sql = "select * from account";

            conn = DriverManager.getConnection("jdbc:mysql:///db1", "root", "root");

            stat = conn.createStatement();

            set = stat.executeQuery(sql);

            while(set.next()){
                int id = set.getInt(1);

                String balance = set.getString("balance");

                System.out.println("员工号为:"+id+"  "+"员工工资为:" + balance);
            }
            if(set !=null){
                System.out.println("查找成功");
            }else{
                System.out.println("查找失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

}

                          4.ResultSet : 结果集对象     封装查询结果

                                next():游标向下移动一行

                                getXxx(参数):获取数据

                                        *Xxx:代表数据类型     如:int getInt(),  String getString()

                                        *参数:

                                                1.int:代表列的编号,从1开始  如:getString(1)

                                                2.String:代表列名称。  如:  getDouble("字段名")

                                        注意:

                                                *使用步骤:1.游标向下移动一行     2.判断是否有数据     3.获取数据

                               5.  Preparedstatement :执行sql的对象

                                        1.SQL注入问题:在拼接sql时,有一些sql的特殊关键字参与字符串的拼接。会造成安全性问题

                                                  *输入用户随便,输入密码:a' or 'a' = 'a'

                                        2.解决sql注入问题:使用Preparedstatement对象来解决

                                        3.预编译的SQL:参数使用?作为占位符

                                   !!! 4.步骤:

                                               1.导入驱动jar包

                                                2.注册驱动

                                                3.获取数据库连接对象 Connection

                                                4.定义sql

                                                        *注意:sql的参数使用?作为占位符。

                                                                如:select * from user where username = ? and password = ?;

                                                5.获取执行sql语句的对象 PerparedStatement    Connection.prepareStatement(String sql)

                                                6.给?赋值:

                                                        方法:setXxx(参数1,参数2)

                                                                参数1:?的位置编号从1开始

                                                                参数2:  ?的值

                                                7.执行sql,接收返回结果,不需要传递sql语句

                                                8.处理结果

                                                9.释放资源

                                

4.抽取JDBC工具类

                            目的:简化书写

                             分析:

                                        1.注册驱动也抽取

                                        2.抽取一个方法获取连接对象

                                                *需求:不想传递参数,还得保证工具类的通用性

                                                *解决:配置文件

                                                        jdbc.properties

                                                                 url=        user=         password=

                                        3.抽取一个方法释放资源  

练习:运用JDBC工具类,简化查询功能          

        1.创建文件,文件里的数据如下:

url=jdbc:mysql:///db1
user=root
password=root
driver=com.mysql.jdbc.driver

         2.创建JDBC工具类

package cn.itcast.jdbc.utils;

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

/**
 * JDBC工具类
 */
public class JDBCUtils {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;
    /**
     * 文件的读取,只读取一次即可,使用静态代码块
     */
    static{

        try {
            //读取资源文件,获取值

            //1.创建properties集合类
            Properties pro = new Properties();

            //获取src路径下的文件的方式---->ClassLoader 类加载器
            ClassLoader classLoader = JDBCUtils.class.getClassLoader();
            URL resource = classLoader.getResource("jdbc.properties");
            String path = resource.getPath();
            System.out.println(path);
            //2.加载文件
            pro.load(new FileReader(path));

            //3.获取数据,赋值
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
            //4.注册驱动
            try {
                Class.forName(driver);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }


    }

    /**
     * 获取连接
     * @return 连接对象
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }

    /**
     * 释放资源
     * @param stat
     * @param conn
     */
    public static void close(ResultSet set,Statement stat, Connection conn){
        if(stat !=null)
        {
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(conn !=null)
        {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(set !=null)
        {
            try {
                set.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }



}

3.改进过后的类代码

package cn.itcast.jdbc;

import cn.itcast.jdbc.utils.JDBCUtils;

import java.sql.*;

/**
 * jdbc练习,演示JDBC工具类
 */
public class JdbcDemo4 {
    public static void main(String[] args) {
        Connection conn =null;
        Statement stat = null;
        ResultSet set = null;
        try {
            //Class.forName("com.mysql.jdbc.Driver");
            String sql = "select * from account";

           // conn = DriverManager.getConnection("jdbc:mysql:///db1", "root", "root");

            conn = JDBCUtils.getConnection();
            stat = conn.createStatement();

            set = stat.executeQuery(sql);

            while(set.next()){
                int id = set.getInt(1);

                String balance = set.getString("balance");

                System.out.println("员工号为:"+id+"  "+"员工工资为:" + balance);
            }
            if(set !=null){
                System.out.println("查找成功");
            }else{
                System.out.println("查找失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
//            if (stat != null) {
//                try {
//                    stat.close();
//                } catch (SQLException e) {
//                    e.printStackTrace();
//                }
//            }
//            if (conn != null) {
//                try {
//                    conn.close();
//                } catch (SQLException e) {
//                    e.printStackTrace();
//                }
            JDBCUtils.close(set,stat,conn);
            }
        }
    }


练习:实现一个简单的登录功能

        需求:通过键盘录入用户名和密码     判断用户是否登录成功



/**
 *  需求:通过键盘录入用户名和密码     判断用户是否登录成功
 *  问题:存在SQL注入问题
 */
public class JdbcDemo5 {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入姓名:");
        String name = scanner.nextLine();
        System.out.println("请输入密码:");
        String password = scanner.nextLine();
        boolean flag = new JdbcDemo5().login(name, password);
        if(flag ==true){
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }
    }

    public boolean login(String name,String password){
        ResultSet res = null;
        Connection conn = null;
        Statement stat = null;
            if(name == null || password ==null)
                 return false;
        try {
             conn = JDBCUtils.getConnection();
            String sql = "select * from user where name='"+name+"' and password='"+password+"'";
            stat = conn.createStatement();
            res = stat.executeQuery(sql);
            return res.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(res,stat,conn);
        }
        return false;
    }


}

 



/**
 *  需求:通过键盘录入用户名和密码     判断用户是否登录成功
 *  注意:解决SQL注入问题
 */
public class JdbcDemo6 {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入姓名:");
        String name = scanner.nextLine();
        System.out.println("请输入密码:");
        String password = scanner.nextLine();
        boolean flag = new JdbcDemo6().login(name, password);
        if(flag ==true){
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }
    }

    public boolean login(String name,String password){
        ResultSet res = null;
        Connection conn = null;
        PreparedStatement pstat = null;
            if(name == null || password ==null)
                 return false;
        try {
             conn = JDBCUtils.getConnection();
            String sql = "select * from user where name= ? and password= ?";
            pstat = conn.prepareStatement(sql);
            pstat.setString(1,name);
            pstat.setString(2,password);
            res = pstat.executeQuery();
            return res.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(res,pstat,conn);
        }
        return false;
    }
}

5.JDBC事务

        1.事务:一个包含多个步骤的业务操作。如果这个业务操作被事务管理,则这多个步骤要么同时成功,要么同时失败。

        2.操作:

                1.开启事务

                2.提交事务

                3.回滚事务

        3.使用Connection对象来管理事务

                *开启事务:setAutoCommit(boolean autoCommit) : 调用该方法设置参数为false,即开启事务

                        **在执行sql之前开启事务

                *提交事务:commit()

                        **当所有sql都执行完提交事务

                *回滚事务:rollback()

                        **在catch中回滚事务

       练习:做一个简单的转账功能



/**
 * jdbc运用事务,一个简单的转账
 */
public class jdbcDemo7 {
    public static void main(String[] args) {
        PreparedStatement pstat1 = null;
        PreparedStatement pstat2 = null;
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            conn.setAutoCommit(false);//开启事务
            String sql1="update  accounts set balance = balance - ? where id = ?";

            String sql2="update  accounts set balance = balance + ? where id = ?";

            pstat1 = conn.prepareStatement(sql1);
            pstat2 = conn.prepareStatement(sql2);

            pstat1.setInt(1,500);
            pstat1.setInt(2,1);

            pstat2.setInt(1,500);
            pstat2.setInt(2,2);


            pstat1.executeUpdate();

            //制造错误
            int i = 3/0;

            pstat2.executeUpdate();
            conn.commit();//提交事务
        } catch (Exception e) {
            try {
                conn.rollback();//回滚事务
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JDBCUtils.close(conn,pstat1);
            JDBCUtils.close(null,pstat2);
        }


    }
}

上一篇:Go tcp客户端、服务端编程


下一篇:Day21 网络编程(下)