jdbc implements connection deletion and change checking

Definition of JDBC

JDBC (Java Data Base Connectivity, java Database Connection) is an Java API used to execute SQL statements. It provides all-in-one access to many relational databases and consists of a set of classes and interfaces written in the Java language.JDBC provides a benchmark from which more advanced tools and interfaces can be built to enable database developers to write database applications.

jdbc Basic Connection

Simply put, the driver is loaded, the connection is established, and then the query and deletion statements are executed. jar package of java.sql is provided in java, but now I am using the connection and instance of mysql, basically the following statement is used in local servers.

Class.forName("com.mysql.jdbc.Driver");

  String url="jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf-8";
  String user="root";
  String password="root";

Loading and establishing a connection is the basic syntax structure. Of course, there are other properties that can be set in the connection database, such as the maximum number of connections, and how to set up a connection pool, which can be used in the configuration. Here I will briefly describe how to connect, followed by the character set of this connection, to prevent random code.

Simple add-delete check

Simple add-delete checks are common to every developer. After all, the real business of our whole system is also these simple logic, but under the connection and coupling of relationships, it will become a hundred times more complex system, so to understand the basic can peek into the construction of larger systems, so now I want to analyze only the basic function implementation.

After you connect the database first, you create a query statement. statment is the keyword used here. Below is the basic implementation of the code.

package dbtest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class DbTest {

 public static void main(String[] args) {
  Employee employee1 =new Employee();
  employee1.setEmpno(555);
  employee1.setEname("hakly");
  employee1.setSal(5400);
  employee1.setHiredate(new Date());

  addEmployee(employee1);
  List<Employee>employees=getEmployees();
  for(Employee employee:employees){
   System.out.println(employee);

  }

  Employee employee =new Employee();
  employee.setEmpno(999);
  employee.setEname("jack");
  employee.setSal(5000);
  employee.setHiredate(new Date());

  addEmployee(employee);

 }

  public static List<Employee> getEmployees() {
  ResultSet rs=null;
  Connection conn=null;
  Statement stat=null;
  List<Employee> employees=new ArrayList<Employee>();
  try{
   Class.forName("com.mysql.jdbc.Driver");


  String url="jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf-8";
  String user="root";
  String password="root";
  conn= DriverManager.getConnection(url,user,password);
  stat=conn.createStatement();
  String sql="select * from emp";
   rs=stat.executeQuery(sql);
   Employee employee=null;
  while(rs.next()){
   employee=new Employee();
   employee.setEmpno(rs.getInt("empno"));
   employee.setEname(rs.getString("ename"));
   employee.setSal(rs.getDouble("sal"));
  employee.setHiredate(rs.getDate("hiredate"));
  employees.add(employee);
  }


  }catch(Exception e ){
   e.printStackTrace();
  }finally{
   try {
    if(conn!=null){
    conn.close();
   } 
    }catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
  return employees;
 }
  public static void addEmployee(Employee employee) {
   Connection conn = null;
   Statement stat = null;
   // 1. Register Driver 
   try {
    Class.forName("com.mysql.jdbc.Driver");

    // 2. Set up a connection 
    String url = "jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf-8";
    String user = "root";
    String password = "root";
    conn = DriverManager.getConnection(url, user, password);

    // 3. Create execution statement, send sql command 
    stat = conn.createStatement();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    String sql = "insert into emp(empno,ename,sal,hiredate) values(" + employee.getEmpno() + ",'"
      + employee.getEname() + "'," + employee.getSal() + ",'" + sdf.format(employee.getHiredate()) + "')";

    // 4. Processing execution results 
    int i = stat.executeUpdate(sql);
   } catch (Exception e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   } finally {
    // 5. close resource 
    try {
     if (conn != null) {
      conn.close();
     }
    } catch (SQLException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }
   }
  }
  public static void updateEmployee(Employee employee) {
   Connection conn = null;
   Statement stat = null;
   // 1. Register Driver 
   try {
    Class.forName("com.mysql.jdbc.Driver");

    // 2. Set up a connection 
    String url = "jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf-8";
    String user = "root";
    String password = "root";
    conn = DriverManager.getConnection(url, user, password);

    // 3. Create execution statement, send sql command 
    stat = conn.createStatement();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    String sql = "update emp set ename='"+employee.getEname()+"empno"+employee.getEmpno()+"sal"+employee.getSal();
    // 4. Processing execution results 
    int i = stat.executeUpdate(sql);
   } catch (Exception e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   } finally {
    // 5. close resource 
    try {
     if (conn != null) {
      conn.close();
     }
    } catch (SQLException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }
   }
  }

}

The code here is cumbersome, but there is no way to do it. In order to get a clearer understanding of the point, this process is necessary, then the next step is to make simple optimizations. Although the code is still similar, it feels more concise. Here's to build a tool class with the following code

package dbtest;

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

public class JdbcUtil {
 static String url = "jdbc:mysql://localhost:3306/xxx?useUnicode=true&characterEncoding=utf-8";
 static String user = "root";
 static String password = "root";

 static{
  try {
   Class.forName("com.mysql.jdbc.Driver");
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 public static Connection getConnection() throws SQLException{
  String url = "jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf-8";
  String user = "root";
  String password = "root";
  return DriverManager.getConnection(url, user, password);

 }
 public static void free(Connection conn){

  try {
   if (conn != null) {
    conn.close();
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 }

Here, database connection and exception handling are combined into a tool class, then the main function is called. This is an object-oriented representation. Of course, the code about the main class will still be analyzed, otherwise it is too empty. The main class code to be analyzed below is as follows

package com.niit.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class EmployeeDao1 {

 public static void main(String[] args) throws ParseException {

  List<Employee> employees=getEmployees();
  for(Employee employee:employees){
   System.out.println(employee);
  }

  /*Employee employee = new Employee();
  employee.setEmpno(9999);
  employee.setEname("tom");
  employee.setSal(6000);

  SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
  employee.setHiredate(sdf.parse("2015-07-23"));

  //addEmployee(employee);
  //updateEmployee(employee);
  deleteEmployee(9999);*/



 }

 public static List<Employee> getEmployees() {
  Connection conn = null;
  Statement stat = null;
  ResultSet rs = null;
  List<Employee> employees = new ArrayList<Employee>();
  // 1. Register Driver 
  try {
   //2. Get Connections 
   conn=JdbcUtil.getConnection();

   // 3. Create execution statement, send sql command 
   stat = conn.createStatement();
   String sql = "select * from emp";

   // 4. Processing execution results 
   rs = stat.executeQuery(sql);
   Employee employee = null;
   while (rs.next()) {
    employee = new Employee();
    employee.setEmpno(rs.getInt("empno"));
    employee.setEname(rs.getString("ename"));
    employee.setSal(rs.getDouble("sal"));
    employee.setHiredate(rs.getDate("hiredate"));

    employees.add(employee);
   }

  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } finally {
   // 5. close resource 
   JdbcUtil.free(conn);
  }
  return employees;
 }

 public static void addEmployee(Employee employee) {
  Connection conn = null;
  Statement stat = null;
  // 1. Register Driver 
  try {
   //2. Get Connections 
   conn=JdbcUtil.getConnection();

   // 3. Create execution statement, send sql command 
   stat = conn.createStatement();
   SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
   String sql = "insert into emp(empno,ename,sal,hiredate) values(" + employee.getEmpno() + ",'"
     + employee.getEname() + "'," + employee.getSal() + ",'" + sdf.format(employee.getHiredate()) + "')";

   // 4. Processing execution results 
   int i = stat.executeUpdate(sql);
  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } finally {
   // 5. close resource 
   JdbcUtil.free(conn);
  }
 }

 public static void updateEmployee(Employee employee) {
  Connection conn = null;
  Statement stat = null;
  // 1. Register Driver 
  try {
   //2. Get Connections 
   conn=JdbcUtil.getConnection();

   // 3. Create execution statement, send sql command 
   stat = conn.createStatement();

   SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
   String sql = "update emp set ename='" + employee.getEname() + "',sal=" + employee.getSal() + ",hiredate='"
     + sdf.format(employee.getHiredate()) + "' where empno=" + employee.getEmpno();

   // 4. Processing execution results 
   int i = stat.executeUpdate(sql);
  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } finally {
   // 5. close resource 
   JdbcUtil.free(conn);
  }
 }

 public static void deleteEmployee(int empno) {
  Connection conn = null;
  Statement stat = null;
  // 1. Register Driver 
  try {
   //2. Get Connections 
   conn=JdbcUtil.getConnection();

   // 3. Create execution statement, send sql command 
   stat = conn.createStatement();
   SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
   String sql = "delete from emp where empno="+empno;

   // 4. Processing execution results 
   int i = stat.executeUpdate(sql);
  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } finally {
   // 5. close resource 
   JdbcUtil.free(conn);
  }
 }

}

This makes it clear that you don't need to split up to see the code, as long as you invoke the method of this class only after you've worked hard, it will be more conducive to checking and troubleshooting, maintaining the indirect goal of a software struggle, although simple optimization can reduce the cost of writing code and maintaining it.

summary

Through this encoding, I have a clearer understanding of the development of object-oriented programming, and of course, a better understanding of the importance of refactoring and optimization. In this software, I learned not only the writing of code, but also the refactoring of code, which requires not only continuous refinement, but also subtle observation of code.