JDBC 的代码逻辑封装

void.bug

已于 2022-02-19 16:53:23 修改

阅读量744

点赞数

分类专栏：设计模式文章标签： sql mysql 数据库

于 2022-02-19 15:11:48 首次发布

空数据

本文链接：https://blog.csdn.net/qq_45205390/article/details/123018820

版权

设计模式专栏收录该内容

13 篇文章 1 订阅

订阅专栏

本文介绍了如何对JDBC进行二次封装，通过定义统一接口和操作类，简化了数据库操作。内容涵盖增加、修改、删除、查询和重置ID等操作，并提供了相应的代码示例和常见错误分析。

摘要由CSDN通过智能技术生成

JDBC 的代码逻辑封装

1.前言

实现Jdbc 的二次封装，只需要传入一个sql 语句即可

相关使用

策略模式
工厂模式

定义操作的db操作类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DbHelper {

    public static final String driver="com.mysql.cj.jdbc.Driver";
    public static final String url="jdbc:mysql://localhost:3306/2109books?useUnicode=true&characterEncoding=utf8";
    public static final String username="root";
    public static final String password="123456";

    public static Connection connection;

    public static Connection getConnection() {
        if(connection == null) {
            try {

                Class.forName(driver);
                connection = DriverManager.getConnection(url, username, password);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return connection;
    }

    public static void close(PreparedStatement pst,ResultSet rst) {
        if (pst != null) {
            try {
                pst.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (rst != null) {
            try {
                rst.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

}

2.定义统一接口

定义操作的相关同意实现接口，方便后续使用

import java.sql.SQLException;

/**
 * Cardinality interface class
 * @param <T> request T info
 * @param <R> response R info
 */
public interface InterfaceAnything<T,R>{
    /**
     * doAnything
     * @param info info
     * @return doAnything
     * @throws Exception 
     */
    R doAnything(T info) throws SQLException, Exception;
}

3. 封装针对用户相关各种操作的类

对于增加/修改/删除的代码相关封装

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;



public class DoAnything implements InterfaceAnything<String, Integer> {

    /**
     * Pass in the SQL statement and return the return type value of ResultSet object
     * @param info info
     * @return ResultSet
     * @throws SQLException sqlExample
     */
    @Override
    public Integer doAnything(String info) throws SQLException {
        System.out.println(info);
        Connection conn=DbHelper.getConnection();
        PreparedStatement pst = null;
        Integer rst = null;
        try {
            pst = conn.prepareStatement(info);
            rst = pst.executeUpdate();         
        } finally {
            DbHelper.close(pst, null);
            conn.close();
        }
        return rst;
    }
}

针对用户花样查询的代码封装

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import enetity.Books;

public class SelectAnything implements InterfaceAnything<String, List<List<String>>> {

    @Override
    public List<List<String>> doAnything(String sql) throws SQLException {
        Connection conn = DbHelper.getConnection();
        List<List<String>> list = new ArrayList<>();
        PreparedStatement pst = conn.prepareStatement(sql);
        ResultSet rst = null ;
        try{
            rst=pst.executeQuery();
            while(rst.next()) {
                int index=1;
                List<String> strs = new ArrayList<>();
                do {
                    try {
                        strs.add(rst.getString(index++));
                        } catch (Exception e) {
                            break;
                        }
                } while (true);
                list.add(strs);
            }
        }finally {
            DbHelper.close(pst, rst);
            conn.close();
        }
        return list;
    }
}

4.针对于各种操作的封装

各种常量的封装

public class ActionName {
    public static final String SELECT_ACTION = "select";
    public static final String ADD_ACTION = "insert";
    public static final String REMOVE_ACTION = "delete";
    public static final String UPDATE_ACTION = "update";
    public static final String ALTER_ACTION = "alter";
}

各种业务逻辑工具类代码实现

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class SqlUntil implements InterfaceAnything<String,InterfaceAnything>{
    private static SqlUntil sqlUntil;
    protected static Map<String, InterfaceAnything> map= new HashMap<>();

    private SqlUntil(){

    }

    static{
        map.put(ActionName.ADD_ACTION,new DoAnything());
        map.put(ActionName.REMOVE_ACTION,new DoAnything());
        map.put(ActionName.UPDATE_ACTION,new DoAnything());
        map.put(ActionName.SELECT_ACTION,new SelectAnything());
        map.put(ActionName.ALTER_ACTION,new DoAnything());
    }

    public static SqlUntil getSqlUntil() throws Exception{
        sqlUntil = new SqlUntil();
        return SqlUntil.sqlUntil;
    }

    @Override
    public InterfaceAnything doAnything(String info) throws Exception {
    // todo sql info
        String UpInfo = info.substring(0,info.indexOf(" "));
        UpInfo=UpInfo.toLowerCase();
        InterfaceAnything interfaceAnything=map.get(UpInfo);
        if (interfaceAnything==null) {
            throw new Exception("SQL statement error, please check your incoming SQL statement");
        } else {
            return interfaceAnything;
        }

    }

}

5.代码测试

5.1 查询操作

代码部分

import java.sql.ResultSet;
import java.util.List;

import com.mysql.cj.jdbc.result.ResultSetImpl;

import enetity.Books;
import until.BookUntil;

public class text {
    public static void main(String[] args) throws Exception {
        SqlUntil sqlObject= SqlUntil.getSqlUntil();
        String sql = "select * from books";
        InterfaceAnything in=sqlObject.doAnything(sql);
        List<List<Object>> one = (List<List<Object>>) in.doAnything(sql);
        for (List<Object> list : one) {
            for (Object object : list) {
                System.out.print(object+"\t");
            }
            System.out.println();
        }
    }
}

输出

27    泰戈尔诗集一    18.00    images/book/book_01.gif    987    0    
28    痕记一    22.80    images/book/book_02.gif    989    1    
29    天堂之旅一    25.00    images/book/book_03.gif    996    1    
30    钱钟书集一    332.50    images/book/book_04.gif    999    1    
31    赵俪生高昭—夫妻回忆录    38.00    images/book/book_05.gif    999    0    
32    无聊斋    28.00    images/book/book_06.gif    999    1    
33    一颗热土豆是一张温馨的床    38.00    images/book/book_07.gif    999    1    
34    李戡戡乱记    22.00    images/book/book_08.gif    999    1    
35    生生世世未了缘    17.50    images/book/book_09.gif    995    1    
36    一生有多少爱    17.50    images/book/book_10.gif    999    0    
37    三生三世    11.00    images/book/16451553643252200.jpg    11    0    
38    22222    22.00    images/book/16451555584682099.jpg    222    0    
39    三生三世2    22.00    images/book/16451638781115412.jpg    22    0    
40    及积极    22.00    images/book/16451639358542448.png    17    1    
41    可乐    22.00    images/book/16451667622797653.jpg    222    0

5.2 增加操作

代码部分

import java.sql.ResultSet;
import java.util.List;

import com.mysql.cj.jdbc.result.ResultSetImpl;

import enetity.Books;
import until.BookUntil;

public class text {
    public static void main(String[] args) throws Exception {
        SqlUntil sqlObject= SqlUntil.getSqlUntil();
        String sql = "insert into books values(default,1,1,1,1,default)";
        InterfaceAnything in=sqlObject.doAnything(sql);
      //   List<Books> list=BookUntil.returnBooks(in.doAnything(sql));
        /*List<List<Object>> one = (List<List<Object>>) in.doAnything(sql);
        for (List<Object> list : one) {
            for (Object object : list) {
                System.out.print(object+"\t");
            }
            System.out.println();
        }*/
        int suess=(int) in.doAnything(sql);
        System.out.println(suess);
    }
}

输出

insert into books values(default,1,1,1,1,default)
1

5.3 修改操作

代码部分

import java.sql.ResultSet;
import java.util.List;

import com.mysql.cj.jdbc.result.ResultSetImpl;

import enetity.Books;
import until.BookUntil;

public class text {
    public static void main(String[] args) throws Exception {
        SqlUntil sqlObject= SqlUntil.getSqlUntil();
        String sql = "update books set bookname = '2' where bookname = '3'";
        InterfaceAnything in=sqlObject.doAnything(sql);
      //   List<Books> list=BookUntil.returnBooks(in.doAnything(sql));
        /*List<List<Object>> one = (List<List<Object>>) in.doAnything(sql);
        for (List<Object> list : one) {
            for (Object object : list) {
                System.out.print(object+"\t");
            }
            System.out.println();
        }*/
        int suess=(int) in.doAnything(sql);
        System.out.println(suess);
    }
}

控制台输出

update books set bookname = '2' where bookname = '3'
1

5.4 删除操作

代码部分

import java.sql.ResultSet;
import java.util.List;

import com.mysql.cj.jdbc.result.ResultSetImpl;

import enetity.Books;
import until.BookUntil;

public class text {
    public static void main(String[] args) throws Exception {
        SqlUntil sqlObject= SqlUntil.getSqlUntil();
        String sql = "delete from books where bookname = '2'";
        InterfaceAnything in=sqlObject.doAnything(sql);
      //   List<Books> list=BookUntil.returnBooks(in.doAnything(sql));
        /*List<List<Object>> one = (List<List<Object>>) in.doAnything(sql);
        for (List<Object> list : one) {
            for (Object object : list) {
                System.out.print(object+"\t");
            }
            System.out.println();
        }*/
        int suess=(int) in.doAnything(sql);
        System.out.println(suess);
    }
}

控制台输出

delete from books where bookname = '2'
1

5.5 重置id操作

代码实现

package text;

public class text2 {
	  public static void main(String[] args) throws Exception {
	        SqlUntil sqlObject= SqlUntil.getSqlUntil();
	        String sql="alter table books auto_increment= 37";
	        InterfaceAnything in=sqlObject.doAnything(sql);
	        int suess=(Integer) in.doAnything(sql);
	        System.out.println(suess);
	    }
}

控制台输出

alter table books auto_increment= 37
11

6. 常见错误

遇到以下类似的错误，为sql语句错误，请到数据库中测试语句，请重新编写 sql语句。

Exception in thread "main" com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: '泰戈尔诗集一'
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1098)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1046)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1371)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1031)
    at text.DoAnything.doAnything(DoAnything.java:27)
    at text.DoAnything.doAnything(DoAnything.java:1)
    at text.text.main(text.java:24)