JDBC基础

最新推荐文章于 2022-04-18 18:20:19 发布

码农の春天

最新推荐文章于 2022-04-18 18:20:19 发布

阅读量798

点赞数

分类专栏： java基础 mysql 文章标签：数据库 jdbc mysql java sql

本文链接：https://blog.csdn.net/qq794554587/article/details/115588655

版权

java基础同时被 2 个专栏收录

32 篇文章 0 订阅

订阅专栏

mysql

5 篇文章 0 订阅

订阅专栏

        //1.注册安装驱动
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/myemployees";
        String user="root";
        String passworld="123456";
        //2.连接数据库
        Connection con = DriverManager.getConnection(url,user,passworld);
        if (con != null){
            System.out.println("数据库登录成功...");
        }else {
            System.out.println("数据库登录失败...");
        }
        String sql="delete from employees where employee_id=206;";

        String sql1="SELECT * FROM `jobs`; ";
        //3.获取发送SQL的对象
        Statement statement=con.createStatement();
        //DML语句返回int类型
        int result=statement.executeUpdate(sql);
        if (result ==1 ){
            System.out.println("执行成功...");
        }else {
            System.out.println("执行失败...");
        }
        //DQL语句返回结果数据集
        ResultSet rs= statement.executeQuery(sql1);
        while (rs.next()){
            String job_id=rs.getString(1);
            String job_title=rs.getString(2);
            String min_salary=rs.getString(3);
            String max_salary=rs.getString(4);
            System.out.println(job_id+"\t"+job_title+"\t"+min_salary+"\t"+max_salary);
        }
        //释放资源。
        rs.close();
        statement.close();
        con.close();

#常见错误

java.lang.ClassNotFoundException:找不到类（类名书写错误、没有导入jar包）

java.sql.SQLException：与sql语句相关的错误（约束错误、表名列名书写错误）：建议：在客户端工具先测试sql语句再粘贴在代码中。

com.mysql.jdbc.exceptions.jdbc.MYSQLSyntaxException:Unknown column原因：列值String类型没有加单引号 ' ' ；

Duplicate entry '1' for key 'PRIMARY'原因，主键值已存在或混乱，更改主键值或清空表

com.mysql.jdbc.exceptions.jdbc4.MYSQLSyntaxErrorException:Unknown column 'password' in 原因可能输入的值的类型不对，确定是否插入的元素时对应的值的类型正确

#SQL注入问题

用户输入的数据中有sql关键字或语法并且参与了sql语句的编译，导致sql语句编译后的条件含义为true ，一直得到正确的结果

#PreparedStatement的应用

作用：预编译sql语句，效率高。安全，避免sql注入。可动态的填充数据，执行多个同构的sql语句。

### 参数标记

PreparedStatement pstmt = con.prepareStatement("select * from user where username =? and password =? ");

JDBC中的所有参数都由？符号站位，被称为参数标记，在执行sql语句前，必须为每个参数提供值。

###动态参数绑定

pstmt=setXxx(小标，值)

PreparedStatement pstmt = con.prepareStatement("select * from user where username =? and password =? ");

pstmt.setString(1,username);

pstmt.setString(2,password);

#封装工具类

##重用性方案

封装获取连接、释放资源两个方法。

提供public static Connection getConnection(){}方法。

提供public static void closeAll(Connection conn,Statement sm,ResultSet rs)(){}方法

import java.sql.*;
public class DBUtils {
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection(){
        Connection connection= null;
        try {
            connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/myemployees","root","123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (statement != null){
                statement.close();
            }
            if (connection != null){
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

##跨平台方案

定义public static final Properties prop = new Properties ( );读取配置文件的Map;定义

static{

     首次使用工具类时，加载驱动

     private static final Properties PROPERTIES=new Properties();

     InputStream is =JDBCUtil.class.getResourceAsStream("路径");

     PROPERTIES.load(is);
     Class.forName(PROPERTIES.getProperty("driver"));

}

新建db.properties文件 内容如下
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myemployees
username=root
password=123456
在DBUtils类中添加语句
    private static final Properties PROPERTIES=new Properties();
    static {
        InputStream is=DBUtils.class.getResourceAsStream("/db.properties");
        try {
            PROPERTIES.load(is);
            Class.forName(PROPERTIES.getProperty("driver"));
        } catch (ClassNotFoundException | IOException e) {
            e.printStackTrace();
        }
    }

#ORM

从数据库查询到结果集ResultSet在进行遍历时，逐行遍历，取出的都是零散的数据。在实际应用开发中，我们需要将零散的数据进行封装整理。

##实体类entity：零散数据的载体

一行数据中，多个零散的数据进行整理

通过entity的规则对表中的数据进行对象的封装

表名=类名；列名=属性名；提供各个属性的get、set方法

提供无参构造方法（视情况添加有参构造）

public class Jobs {
    private String job_id;
    private String job_title;
    private int min_salary;
    private int max_salary;
    public Jobs() {
    }
    public Jobs(String job_id, String job_title, int min_salary, int max_salary) {
        this.job_id = job_id;
        this.job_title = job_title;
        this.min_salary = min_salary;
        this.max_salary = max_salary;
    }
    @Override
    public String toString() {
        return "jobs{" +
                "job_id='" + job_id + '\'' +
                ", job_title='" + job_title + '\'' +
                ", min_salary=" + min_salary +
                ", max_salary=" + max_salary +
                '}';
    }
    public String getJob_id() {
        return job_id;
    }
    public void setJob_id(String job_id) {
        this.job_id = job_id;
    }
    public String getJob_title() {
        return job_title;
    }
    public void setJob_title(String job_title) {
        this.job_title = job_title;
    }
    public int getMin_salary() {
        return min_salary;
    }
    public void setMin_salary(int min_salary) {
        this.min_salary = min_salary;
    }
    public int getMax_salary() {
        return max_salary;
    }
    public void setMax_salary(int max_salary) {
        this.max_salary = max_salary;
    }
}

        while (rs.next()){
            String job_id=rs.getString(1);
            String job_title=rs.getString(2);
            int min_salary=rs.getInt(3);
            int max_salary=rs.getInt(4);
            Jobs jobs=new Jobs();
            jobs.setJob_id(job_id);
            jobs.setJob_title(job_title);
            jobs.setMin_salary(min_salary);
            jobs.setMax_salary(max_salary);
            System.out.println(jobs);
        }

#DAO数据访问对象（Data Access Object）

DAO实现了业务逻辑与数据库访问相分离

对同一张表的所有操作封装在XxxDaolmpl对象中。

根据增删改查的不同功能实现具体的方法（insert、update、delele、select、selectAll）

public class JobsDaoImpl {
    public int insert(Jobs jobs){
        String sql="insert into jobs(job_id,job_title,min_salary,max_salary)values(?,?,?,?);";
        Connection connection=null;
        PreparedStatement preparedStatement= null;
        try {
            connection=DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, jobs.getJob_id());
            preparedStatement.setString(2, jobs.getJob_title());
            preparedStatement.setInt(3,jobs.getMin_salary());
            preparedStatement.setInt(4,jobs.getMax_salary());
            int result=preparedStatement.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection,preparedStatement,null);
        }
        return 0;
    }
    public int delete(String job_id){
        String sql="delete from jobs where job_id=?;";
        Connection connection = null;
        PreparedStatement preparedStatement= null;
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,job_id);
            int result= preparedStatement.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection,preparedStatement,null);
        }
        return 0;
    }
    public int update(Jobs jobs){
        String sql="update jobs set job_title=?,min_salary=?,max_salary=? where job_id=?";
        Connection connection=null;
        PreparedStatement preparedStatement= null;
        try {
            connection=DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,jobs.getJob_title());
            preparedStatement.setInt(2,jobs.getMin_salary());
            preparedStatement.setInt(3,jobs.getMax_salary());
            preparedStatement.setString(4,jobs.getJob_id());
            int result= preparedStatement.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection,preparedStatement,null);
        }
        return 0;
    }
    public Jobs select(String job_id){
        String sql="select * from jobs where job_id=?";
        Connection connection=null;
        PreparedStatement preparedStatement= null;
        ResultSet resultSet=null;
        Jobs jobs=null;
        try {
            connection=DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,job_id);
            resultSet= preparedStatement.executeQuery();
            if (resultSet.next()){
                jobs=new Jobs();
                String j_id=resultSet.getString("job_id");
                String j_title=resultSet.getString("job_title");
                int j_min=resultSet.getInt("min_salary");
                int j_max=resultSet.getInt("max_salary");
                jobs.setJob_id(j_id);
                jobs.setJob_title(j_title);
                jobs.setMin_salary(j_min);
                jobs.setMax_salary(j_max);
            }
            return jobs;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection,preparedStatement,resultSet);
        }
        return null;
    }
    public List<Jobs> selectAll(){
        String sql ="select * from jobs;";
        Connection connection = null;
        PreparedStatement preparedstatement= null;
        ResultSet resultSet=null;
        Jobs jobs=null;
        List<Jobs> jobsList=new ArrayList<>();;
        try {
            connection = DBUtils.getConnection();
            preparedstatement = connection.prepareStatement(sql);
            resultSet=preparedstatement.executeQuery();
            while (resultSet.next()){
                String j_id=resultSet.getString("job_id");
                String j_title=resultSet.getString("job_title");
                int j_min=resultSet.getInt("min_salary");
                int j_max=resultSet.getInt("max_salary");
                jobs=new Jobs(j_id,j_title,j_min,j_max);
                jobsList.add(jobs);
            }
            return jobsList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection,preparedstatement,resultSet);
        }
        return null;
    }
}

#Date 工具类

java.sql.Date和java.util.Date不能通用，需要转换

##SimpleDateFormat

格式化和解析日期的工具类。允许进行格式化（日期 --> 文本）、解析（文本-->日期）和规范化。

SimpleDateFormat sdf=new SimpleDateFormat("yyyy-mm-dd");
java.util.Date date=sdf.parse(String dateStr);
String dates=sdf.format(date);

public class DateUtils {
    private static final SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-mm-dd");
    //字符串转Util
    public static java.util.Date strToUtilDate(String str){
        try {
            return simpleDateFormat.parse(str);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }
    //util转sql
    public static java.sql.Date utilToSql(java.util.Date date){
        return new java.sql.Date(date.getTime());
    }
    //util转字符串
    public static String toStr(java.util.Date date){
        return simpleDateFormat.format(date);
    }
}

#Service 业务逻辑层

业务经常可以由一个或多个DAO的调用组成。

##编写service实现转账功能

#事务

在JDBC中，获得Connection对象开始事务--提交或回滚--关闭连接。其事务策略是

connection.setAutoCommit(false);//关闭自动回滚
connection.commit();
connection.rollback();

##service层控制事务

会出现过个connection对象，避免这个问题就引出了ThreadLocal

#事务的封装

##完善工具类

public class DBUtils {
    private static final Properties PROPERTIES=new Properties();
    private static ThreadLocal<Connection> t1=new ThreadLocal<>();
    static {
        InputStream is=DBUtils.class.getResourceAsStream("/db.properties");
        try {
            PROPERTIES.load(is);
            Class.forName(PROPERTIES.getProperty("driver"));
        } catch (ClassNotFoundException | IOException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection(){
        Connection connection= t1.get();
        try {
            if (connection ==null){
                connection=DriverManager.getConnection(PROPERTIES.getProperty("url"), PROPERTIES.getProperty("username"), PROPERTIES.getProperty("password") );
            t1.set(connection);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    //开启事务
    public static void begin(){
        Connection connection=getConnection();
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //提交事务
    public static void commit(){
        Connection connection=getConnection();
        try {
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection,null,null);
        }
    }
    //回滚事务
    public static void rollback(){
        Connection connection=getConnection();
        try {
            connection.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection,null,null);
        }
    }
    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (statement != null){
                statement.close();
            }
            if (connection != null){
                connection.close();
                t1.remove();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

码农の春天

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
JDBC基础

//1.注册安装驱动 Class.forName("com.mysql.jdbc.Driver"); String url="jdbc:mysql://localhost:3306/myemployees"; String user="root"; String passworld="123456"; //2.连接数据库 Connection con = DriverManager.getCon...
复制链接

扫一扫

专栏目录