JDBC (Tool Class, CRUD, Dao Mode, PrepareStatement)

Posted by justin.nethers on Fri, 09 Aug 2019 12:17:30 +0200

JDBC

JAVA Database Connectivity Java database connection
To put it bluntly: JDBC is a kind of database access rules and specifications provided by Sun Company. Because there are many kinds of databases and the java language is widely used, Sun provides a specification for other database providers to implement the underlying access rules. Our java program can only use jdbc driver provided by sun company.

Basic steps for using JDBC

  • Import driver
    The java project file (that is, the peoject you created) is right-clicked - > Build Path - > Configure Build Path - > Libraries - > Add External JARS - > select mysql-connector-java-8.0.16.jar (I downloaded version 8.0.16)
  • Registration Driver
	DriverManager.registerDriver(new com.mysql.jdbc.Driver());
  • Establish a connection
	Connection conn = null;
	conn = DriverManager.getConnection("jdbc:mysql://Localhost / javatest? Server Timezone = UTC ","username","password";
  • Create Statement
	Statement st = null;
	st = conn.createStatement();
  • Execute sql to get ResultSet
//Take queries as an example
String sql = "select * from peo";
rs = st.executeQuery(sql);
  • Traversal result set
String sql = "select * from peo";
rs = st.executeQuery(sql);
while(rs.next()) {
		String id = rs.getString("Pno");
		String name = rs.getString("Pname");
		String pwd = rs.getString("Paswd");
		System.out.println("id:"+id+" name:"+name+" pwd:"+pwd);
}
  • Releasing resources
//Release order: rs-> st-> conn;
//For example, conn
try {
		if(conn != null) {
		conn.close();
		System.out.println("conn Successful closure");
}}catch (SQLException e) {
		e.printStackTrace();
}finally {
		conn = null;
}

Writing a java file above is really bloated. All the JDBC tool classes introduced can make the code more flexible.

JDBC Tool Class Creation

1. Integration of Resource Release Work
The resource release code is written in a tool class JDBCUtil.java
JDBCUtil.java

	/*
	 * Releasing resources
	 * */
	
	public static void release(Connection conn,Statement st,ResultSet rs) {
		closeRs(rs);
		closeSt(st);
		closeConn(conn);
	}
	private static void closeRs(ResultSet rs) {
		try {
			if(rs != null) {
				rs.close();
				System.out.println("rs Successful closure");
			}
		} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
		}finally {
			rs = null;
		}
	}
	private static void closeSt(Statement st) {
		try {
			if(st != null) {
				st.close();
				System.out.println("st Successful closure");
			}
		} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
		}finally {
			st = null;
		}
	}
	private static void closeConn(Connection conn) {
		try {
			if(conn != null) {
				conn.close();
				System.out.println("conn Successful closure");
			}
		} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
		}finally {
			conn = null;
		}
	}

Then call the release function directly in the main program final.

finally {
		JDBCUtil.release(conn, st, rs);
}

2. Drive prevention secondary registration
Driver class contains a static code base, which contains a section of code that is
  java.sql.DriverManager.registerDriver(new Driver());
It was executed at first, so it's equivalent to registering two drivers. In fact, it is not necessary.
Finally, we usually use:
  Class.forName("com.mysql.cj.jdbc.Driver");

3. Use properties configuration files

  • Declare a file jdbc.properties under src. The contents (no quotes, no semicolons) are as follows:
	driverClass = com.mysql.cj.jdbc.Driver
	url = jdbc:mysql://localhost/javatest?serverTimezone=UTC
	name = root
	passwd = 52151
  • In the tool class, use static code blocks to read attributes
static String driverClass = null;
static String url = null;
static String name = null;
static String passwd = null;
static {
	try {
		//Create a property configuration object
		Properties properties = new Properties();
		InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
			//Import Input Flow
		properties.load(is);
			//read attribute
		driverClass = properties.getProperty("driverClass");
		url = properties.getProperty("url");
		name = properties.getProperty("name");
		passwd = properties.getProperty("passwd");
	}
	catch (Exception e) {
		e.printStackTrace();
	}
}

CRUD operation of database

  • Insert
//insert returns the number of rows affected > 0 if the operation succeeds or fails
sql = "INSERT INTO peo VALUES(\"5\",\"ds5\",\"1513213\");";
int results = st.executeUpdate(sql);
if(results > 0 ) {
	 System.out.println("Added Successfully");
}else {
	 System.out.println("Failure to add");
}
  • Delete
sql = "DELETE FROM peo where Pno= '5';";
results = st.executeUpdate(sql);
if(results > 0 ) {
	System.out.println("Successful deletion");
}else {
	System.out.println("Delete failed");
}
  • Query
String sql = "select * from peo";
rs = st.executeQuery(sql);
//Traversal result set
while(rs.next()) {
	 String id = rs.getString("Pno");
	 String name = rs.getString("Pname");
	 String pwd = rs.getString("Paswd");
     System.out.println("id: "+id+" name: "+name+" pwd: "+pwd);
}
  • Update
sql = "UPDATE peo SET Paswd = '88889' WHERE Pno = '4';";
results = st.executeUpdate(sql);
if(results > 0 ) {
	 System.out.println("Update Successful");
}else {
     System.out.println("Update failed");
}

Using unit testing JUNIT to test code steps

  1. Define a class, such as TestDemo.java, which defines the method testXXX. It must start with test.
  2. Adding Junit support
    Right-click Project - > Add Library - > Junit - > Junit4/5
    Note that you added it to Classpath
  3. With annotations to the method, it's actually a tag that Junit doesn't know without the @Test tag.
	@Test
   		public void testQuery() {
   			...
   		}
  1. The cursor selects the method name and right-clicks the unit test (run as). Or open the outline attempt and select the method name to execute.

Dao model

Date Access Object Data Access Object

  1. Create a new dao interface peoDao.java, which declares database access rules
package com.yl.dao.demo;

import java.util.List;
import java.util.Map;

public interface peoDao {
	//Query all
	void findAll();
	//Sign in
	void login(String username,String password);
	//Add to
	void insert(String username,String password);
	//delete
	void delete(String id);
	//Update user name based on id
	void update(String id,String username);
	//Traverse the output by returning a List
	List< Map<String,String> > findall();
	//Return to int to get several rows affected
	int delete1(String id);
}
  1. Create a new dao implementation class peoDaoImpl.java to implement the previously defined rules
package com.yl.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.yl.dao.demo.peoDao;
import com.yl.dao.util.JDBCUtil;

public class peoDaoImpl implements peoDao {

	@Override
	public void findAll() {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// Get the connection object
			conn = JDBCUtil.getConn();
			// Create Statement
			st = conn.createStatement();
			String sql = "select * from p_user";
			rs = st.executeQuery(sql);
			// Traversal result set
			while (rs.next()) {
				String id = rs.getString("Pno");
				String name = rs.getString("Pname");
				String pwd = rs.getString("Paswd");
				System.out.println("id: " + id + " name: " + name + " pwd: " + pwd);
			}

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.release(conn, st, rs);
		}

	}

	@Override
	public void login(String username, String password) {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// TODO Auto-generated method stub
			// Get the connection object
			conn = JDBCUtil.getConn();
			// Create Statement
			String sql = "select * from p_user where Pname = ? and Paswd = ?";
			// Execute the grammar check of sql statement beforehand, and the corresponding content will be treated as a string no matter what comes in later.
			PreparedStatement ps = conn.prepareStatement(sql);// Safer than Statement
			// ? The corresponding index starts from 1.
			ps.setString(1, username);
			ps.setString(2, password);
			rs = ps.executeQuery();
			// Traversal result set
			if (rs.next()) {
				System.out.println("Successful login");
			} else
				System.out.println("Landing Failure");

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.release(conn, st, rs);
		}
	}

	@Override
	public void insert(String username, String password) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement ps = null;
		int res;
		try {
			// TODO Auto-generated method stub
			// Get the connection object
			conn = JDBCUtil.getConn();
			// Create Statement
			String sql = "insert into p_user values('10' , ? , ?);";
			// Execute the grammar check of sql statement beforehand, and the corresponding content will be treated as a string no matter what comes in later.
			ps = conn.prepareStatement(sql);// Safer than Statement
			// ? The corresponding index starts from 1.
			ps.setString(1, username);
			ps.setString(2, password);
			res = ps.executeUpdate();
			// Traversal result set
			if (res > 0) {
				System.out.println("Successful insertion");
			} else
				System.out.println("Insertion failure");

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.release(conn, ps);
		}
	}

	@Override
	public void delete(String id) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement ps = null;
		int res;
		try {
			// TODO Auto-generated method stub
			// Get the connection object
			conn = JDBCUtil.getConn();
			// Create Statement
			String sql = "delete from p_user where Pno = ?";
			// Execute the grammar check of sql statement beforehand, and the corresponding content will be treated as a string no matter what comes in later.
			ps = conn.prepareStatement(sql);// Safer than Statement
			// ? The corresponding index starts from 1.
			ps.setString(1, id);
			res = ps.executeUpdate();
			// Traversal result set
			if (res > 0) {
				System.out.println("Successful deletion");
			} else
				System.out.println("Delete failed");

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.release(conn, ps);
		}
	}

	@Override
	public void update(String id, String username) {

		Connection conn = null;
		PreparedStatement ps = null;
		int res;
		try {
			// TODO Auto-generated method stub
			// Get the connection object
			conn = JDBCUtil.getConn();
			// Create Statement
			String sql = "update p_user set Pname = ? where Pno = ? ";
			// Execute the grammar check of sql statement beforehand, and the corresponding content will be treated as a string no matter what comes in later.
			ps = conn.prepareStatement(sql);// Safer than Statement
			// ? The corresponding index starts from 1.
			ps.setString(1, username);
			ps.setString(2, id);
			res = ps.executeUpdate();
			// Traversal result set
			if (res > 0) {
				System.out.println("Update Successful");
			} else
				System.out.println("Update failed");

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.release(conn, ps);
		}
	}

	@Override
	public List<Map<String, String>> findall() {
		List< Map<String,String> > peos= new ArrayList<>();
		Map<String,String> peo = new HashMap<>();
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// TODO Auto-generated method stub
			// Get the connection object
			conn = JDBCUtil.getConn();
			// Create Statement
			st = conn.createStatement();
			String sql = "select * from p_user";
			rs = st.executeQuery(sql);
			// Traversal result set
			while (rs.next()) {
				String id = rs.getString("Pno");//id
				String name = rs.getString("Pname");//name
				peo.put(id,name);
				//System.out.println("id: " + id + " name: " + name + " pwd: " + pwd);
			}
			peos.add(peo);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.release(conn, st, rs);
		}
		return peos;
	}

	@Override
	public int delete1(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		int res = 0;
		try {
			// TODO Auto-generated method stub
			// Get the connection object
			conn = JDBCUtil.getConn();
			// Create Statement
			String sql = "delete from p_user where Pno = ?";
			// Execute the grammar check of sql statement beforehand, and the corresponding content will be treated as a string no matter what comes in later.
			ps = conn.prepareStatement(sql);// Safer than Statement
			// ? The corresponding index starts from 1.
			ps.setString(1, id);
			res = ps.executeUpdate();

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.release(conn, ps);
		}
		return res;
	}
}
  1. Create a new TestDemoDaoImpl direct-use implementation
package com.yl.dao.test;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.junit.Test;

import com.yl.dao.demo.peoDao;
import com.yl.dao.impl.peoDaoImpl;

public class TestUserDaoImpl {
	@Test
	public void testFindAll() {
		peoDao dao = new peoDaoImpl();
		dao.findAll();
	}
	@Test
	public void testLogin() {
		peoDao dao = new peoDaoImpl();
		dao.login("yl", "123456");
//		dao.login("yl","123456 ' or '1=1'");
	}
	@Test
	public void testInsert() {
		peoDao dao = new peoDaoImpl();
		dao.insert("zk", "123147");
	}
	@Test
	public void testDelete() {
		peoDao dao = new peoDaoImpl();
		dao.delete("10");
	}
	@Test
	public void testUpdate() {
		peoDao dao = new peoDaoImpl();
		dao.update("4", "hhh");
	}
	@Test
	public void testFindall() {
		List< Map<String,String> > peos = new ArrayList<>();
		peoDao dao = new peoDaoImpl();
		peos = dao.findall();
		for(Map<String, String> map : peos) {
			Iterator<String> it = map.keySet().iterator();
			while(it.hasNext()) {
				String key = it.next();
				System.out.println("id:"+key+" name:"+map.get(key));
			}
		}
	}
	@Test 
	public void testDelete1() {
		peoDao dao = new peoDaoImpl();
		int ans = dao.delete1("5"); //There are three in the mysql table
		System.out.println(ans+"Row receives impact"); //Completion
	}
}

Statement security issues

  1. Statement execution is actually splicing sql statements. Splice sql statements first, and then execute them together.
SELECT * FROM peo WHERE username='admin' AND PASSWORD='100234khsdf88' or '1=1' 
Before splicing sql statements, if the variables contain database keywords, then they are considered keywords. I don't think it's a normal string.

So PrepareStatement is generally used.

PrepareStatement

This object replaces the previous state object.

Compared with previous statements, a given sql statement is preprocessed and grammar checked. Use? Placeholders in sql statements to replace subsequent variables to be passed in. The value of the variable that comes in later will be treated as a string and will not produce any keywords.

String sql = "insert into t_user values(null , ? , ?)";
ps = conn.prepareStatement(sql);
//Assign placeholders from left to right. 1 represents the first question mark. You always start with 1.
ps.setString(1, userName);
ps.setString(2, password);

End？maybe......

Topics: SQL Java JDBC Database

Hot Topics

©2024 Programmer Think