Previously, I learned how to create a database tool class, as follows:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBUtil {
    // Privatization Construction Method
    private DBUtil() {
    }

    private static String url = "";
    private static String user = "";
    private static String password = "";
    private static String className = "";
    static {
        try {
            Class.forName(className);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    // Get Connected
    public static Connection getConn() {
        @SuppressWarnings("unused")
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {

            e.printStackTrace();
        }

        return null;
    }

    // Close the connection
    public static void close(ResultSet rs, Statement stm, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (stm != null) {
            try {
                stm.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
    }
}

Now the main thing is to learn how to use this tool class. Simply understand the implementation process of CURD.

In Baidu Encyclopedia, CURD is explained as follows:

Er, in a word, add, delete and revise.

I. Increasing Operations

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

//Adding operations
public class Test3 {
    static int add(StuInfo stu) {
        int result = 0;

        Connection conn = null;
        Statement stm = null;

        try {
            conn = DBUtil.getConn();
            stm = conn.createStatement();
            String sql = "insert into stuInfo(name,age,shoolId) values('"
                    + stu.getName() + "'," + stu.getAge() + ","
                    + stu.getSchoolId() + ")";// Don't forget to add single quotation marks to string types
            result = stm.executeUpdate(sql);// The return value represents the number of rows affected in the database
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBUtil.close(null, stm, conn);
        }
        return result;
    }

    public static void main(String[] args) {
        StuInfo stu = new StuInfo();
        stu.setName("A third");
        stu.setAge("25");
        stu.setSchoolId(3);

        int result = add(stu);
        if (result > 0) { // Write here result=1，Compiler fails
            System.out.println("Successful operation");
        } else {
            System.out.println("operation failed");
        }

    }
}

2. Delete operation

//Delete operation
static int delStuById(int id){
    int result=0;
    Connection conn=null;
    Statement stm=null;
    
    try{
        conn=DBUtil.getConn();
        stm=conn.createStatement();
        String sql="delete from stuInfo where id=4"+id;
        result=stm.executeUpdate(sql);
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        DBUtil.close(null,stm,conn)
    }
    return result;
}

3. Update operation

//update operation
static int updateStudent(StuInfo stu) {
    int result = 0;
    Connection conn = null;
    Statement stm = null;
    try {
        conn = DBUtil.getConn();
        stm = conn.createStatement();
        String sql = "update stuInfo set name='" + stu.getName() + "',age="
                    + stu.getAge() + ",schoolId=" + stu.getSchoolId()
                    + " where id=" + stu.getId() + " ";
        result=stm.executeUpdate(sql);
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            DBUtil.close(null, stm, conn);
        }
    return result;
    }

4. Search operation

// Query operation
    static StuInfo getStuById(int id){
        StuInfo stu=null;
        
        Connection conn=null;
        ResultSet rs=null;
        Statement stm=null;
        
        try{
            conn=DBUtil.getConn();
            stm=conn.createStatement();
            String sql="select * from stuInfo where id="+id;
            rs=stm.executeQuery(sql);
            if(rs.next()){
                stu=new StuInfo();
                stu.setId(rs.getInt("id"));
                stu.setName(rs.getString("name"));
                stu.setAge(rs.getInt("age"));
                stu.setSchoolId(rs.getInt("schoolId"));
            }
            
        }catch(Exception ex){
            ex.printStackTrace();
        }finally{
            DBUtil.close(rs,stm,conn);
        }
        
        return stu;
    }

Find the list

//Query out the list
    public static List<StuInfo> getAllStudent(){
        List<StuInfo> stuList=new ArrayList<StuInfo>();
        
        Connection conn=null;
        ResultSet rs=null;
        Statement stm=null;
        
        try{
            conn=DBUtil.getConn();
            stm=conn.createStatement();
            String sql="select * from stuInfo " ;
            rs=stm.executeQuery(sql);
            while(rs.next()){
                StuInfo stu=new StuInfo();
                stu.setId(rs.getInt("id"));
                stu.setName(rs.getString("name"));
                stu.setAge(rs.getInt("age"));
                stu.setSchoolId(rs.getInt("schoolId"));
                
                stuList.add(stu);
            }
            
        }catch(Exception ex){
            ex.printStackTrace();
        }finally{
            DBUtil.close(rs,stm,conn);
        }
        
        return stuList;
    }
    
    public static void main(String[] args) {
        List<StuInfo> stuList=getAllStudent();
        for(int i=0;i<stuList.size();i++){
            System.out.println(stuList.get(i));
        }
        
    }

5. SQL Injection Attack

This vulnerability is caused by the use of string splicing in sql statement parameterization.

For example, injection string: 1'or'1'='1, which makes password security missing.

Solve:

String sql="select * from admininfo where userName ='"+userName+"' and password='"+password+"' ";

Prepared Statement

Statement will produce unexpected results when it contains special characters or keywords of SQL (e.g.'or 1 or'), which can be solved by PreparedStatement.

PreparedStatement is an extension of Statement

Advantage:

1) Solving sql injection attacks

2) Statement can cause database to compile sql frequently, which may cause data buffer overflow

3) Database and driver to optimize PreparedStatement

Code example:

// Injection-proof login
public static AdminInfo getLoginAdmin(String userName,String password){
    AdminInfo admin=null;
    Connection conn=null;
    ResultSet rs=null;
    PreparedStatement stm=null; //First
    
    
    try{
        conn=DBUtil.getConn();
        String sql="select * from adminInfo where userName=? and password=?" ; //Second: Change to placeholder representation
        stm=conn.preparedStatement(sql);//Third: Attention,Reference is needed in the enlargement,This code should be placed before the following two
        stm.setString(1,userName);//Four Places: Passing on Reference
        stm.setString(2,password);
        
        rs=stm.executeQuery();//Fifth: If you use preparedStatement，Don't pass on reference here
        
        if(rs.next()){
            admin=new AdminInfo();
            admin.setId(rs.getInt("id"));
            admin.setUserName(rs.getString("userName"));
            admin.setPassword(rs.getString("password"));
            admin.setAddress(rs.getString("address"));
        }
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        DBUtil.close(rs,stm,sonn);
    }
    return admin;
}

6. Several Common Special Types

1).DATA,TIME,TIMESTAMP-> date,time,datetime

Storage: stm.setDate(i,d); stm.setTime(i,t); stm.setTimestamp(i, ts);

Take: rs.getDate("birthday"); rs.getTime(i); rs.getTimestamp(i);

2).CLOB -> text

Save: ps.setCharacterStream(index, reader, length);

ps.setString(i, s);

Take: reader = rs. getCharacterStream(i);

reader = rs.getClob(i).getCharacterStream();

string = rs.getString(i);

3).BLOB -> blob

Save: ps.setBinaryStream(i, inputStream, length);

Take: rs.getBinaryStream(i);

rs.getBlob(i).getBinaryStream

Demo 1: Date type

//Date type
//java.sql.Date yes java.util.Date Subclasses
public static int add(AdminInfo admin){
    int result = 0;
    Connection conn = null;
    PreparedStatement stm = null;
    try {
        conn = DBUtil.getConn();
        String sql="insert into adminInfo (userName,password,address, createDate,birthday) values (?,?,?,?,?) ";
        stm = conn.prepareStatement(sql);
        stm.setString(1, admin.getUserName());
        stm.setString(2, admin.getPassword());
        stm.setString(3, admin.getAddress());
        //stm.setDate(4,admin.getCreateDate());//What you want is subclasses,Parents can't pass in
        stm.setDate(4, new java.sql.Date(admin.getCreateDate().getTime()));  //Can be processed with string type data
        stm.setString(5, admin.getBirthday());
        result=stm.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
            } finally {
                DBUtil.close(null, stm, conn);
                }
    return result;    
    }
}

public static void main(String[] args) {
    AdminInfo admin=new AdminInfo();
    admin.setUserName("A third");
    admin.setPassword("123");
    admin.setAddress("Wenyi West Road, Hangzhou");
    admin.setCreateDate(new Date());
    admin.setBirthday("1999-03-05 12:20:40");
    int result=add(admin);
    if(result>0){
        System.out.println("Success");
        }else{
            System.out.println("fail");
            }
    }

Demo 2: Large Text Processing

    //take c Documents on disk  index.html Read into the database
    static int addContent(){
        int result=0;
        Connection conn=null;
        PreparedStatement stm=null;
        try{
            conn=DBUtil.getConn();
            String sql="insert into t_testclob (content) values (?)"; 
            stm=conn.prepareStatement(sql);
            
            Reader br=new BufferedReader(new FileReader("c:\\index.html"));
            stm.setCharacterStream(1, br);
            
            result=stm.executeUpdate();
            
            br.close();
                
            
        }catch(Exception ex){
            ex.printStackTrace();
        }finally{
            DBUtil.close(null, stm, conn);
        }
        
        return result;
    }

Demo 3: Read the file and save it to the specified location

      //hold text Type of data,Read it out,Save to e disc
        static void getContent(){
        Connection conn=null;
        PreparedStatement stm=null;
        ResultSet rs=null;
        try{
            conn=DBUtil.getConn();
            String sql="select * from t_testclob";
            stm=conn.prepareStatement(sql);
            rs=stm.executeQuery();
            
            while(rs.next()){
                BufferedReader r=new BufferedReader(rs.getCharacterStream("content")); 
                
                BufferedWriter bw=new BufferedWriter(new FileWriter("e:\\testxxx.txt"));
                String str=null;
                while((str=r.readLine())!=null){
                    bw.write(str);
                    bw.newLine();
                    bw.flush();
                }
                
                bw.close();
                r.close();
            }
            
            
        }catch(Exception ex){
            ex.printStackTrace();
        }finally{
            DBUtil.close(null, stm, conn);
        }
    }

Demo 4: Reading pictures from a database

public static void getPhoto(){
    ResultSet rs=null;
    Connection conn=null;
    PreparedStatement stm=null;
    try{
        conn=DBUtil.getConn();
        String sql="select * from t_testblob";
        stm=conn.prepareStatement(sql);
        rs=stm.executeQuery();
                        
        int i=0;
        while(rs.next()){
            //    rs.getString("content");
            InputStream in=rs.getBinaryStream("photo");
            OutputStream out=new BufferedOutputStream(new FileOutputStream("e:\\test"+i++ +".bmp" ));
            byte [] buff=new byte[1024];
            int len=0;
            while((len=in.read(buff))!=-1){
                out.write(buff,0,len);
                }
            out.close();
            in.close();
            }
        }catch(Exception ex){
            ex.printStackTrace();
            }finally{
                DBUtil.close(rs, stm, conn);
                }
    }

Demo 5: Read a picture to the database

public static int addPhoto(){
    int result=0;
    Connection conn=null;
    PreparedStatement stm=null;
    try{
        conn=DBUtil.getConn();
        String sql="insert into t_testblob (name,photo) values (?,?)"; 
        stm=conn.prepareStatement(sql); 
                        
        InputStream in=new BufferedInputStream(new FileInputStream("c://bigPhoto.bmp")); 
                        
        stm.setString(1, "Bighead");
        stm.setBinaryStream(2,in);
                        
        result=stm.executeUpdate();
        in.close();
            
    }catch(Exception ex){
        ex.printStackTrace();
        }finally{
            DBUtil.close(null, stm, conn);
            }
    return result;
    }

Additional:

Image or binary file (Blob)

In mysql, it's called blob. In SQL server, it's called image. Ordinary blob is 64k. If the file exceeds 1M, it appears

Packet for query is too large (3781053 > 1048576). You can change this value on the server by setting the max_allowed_packet variable.

Solve:

mysql has a system parameter max_allowed_package whose default value is 1048576(1M).

In my.ini, find max_allowed_packet whose default value is 1M, which can be increased.

Demo 6: Using Configuration Files

//stay DBUtil Settings in
private static String url;
private static  String user;
private static String password;
private static String className;
static{
    try {
        //Read configuration files
        Properties settings =new Properties();
        InputStream in=new FileInputStream("src/dbconfig.properties");
        settings.load(in);
        
        url=settings.getProperty("url");
        user=settings.getProperty("user");
        password=settings.getProperty("password");
        className=settings.getProperty("className");
        
        System.out.println(url);
        
        Class.forName(className); //Load driver class  //If not guided jar package Could not initialize class util.DBUtil
        
    } catch (ClassNotFoundException | IOException e) {//stay Java This can be done as described in Core Foundation 1
        throw new RuntimeException(e);
        }
    }