- Extract execution update method
- Extract query method – ResultSetMetaData
<strong>ResultSetMetaData rsmd = rs.getMetaData();//Metadata, structural data of the result set</strong>- Extract query method – parse the result set and encapsulate it into an entity object
- Extract methods to obtain connections and release resources
- Transfer database configuration information to configuration file
<dependencies> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.10</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.28</version> </dependency> </dependencies>package com.csdn.fruit.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import java.io.Serializable; @Data @NoArgsConstructor @AllArgsConstructor public class Fruit implements Serializable { private Integer fid; private String fname; private Integer price; private Integer fcount; private String remark; public Fruit(String fname, Integer price, Integer fcount, String remark) { this.fname = fname; this.price = price; this.fcount = fcount; this.remark = remark; } @Override public String toString() { return fname + "\t\t" + price + "\t\t" + fcount + "\t\t" + remark; } }
package com.csdn.mymvc.dao; import com.csdn.mymvc.util.ClassUtil; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.ParameterizedType; import java.lang.reflect.Type; import java.sql.*; import java.util.ArrayList; import java.util.List; import java.util.Properties; public abstract class BaseDao<T> { private String DRIVER; private String URL; private String USER; private String PWD; private String entityClassName; public BaseDao() { //Who is this? This represents the instance object of FruitDaoImpl. Because BaseDao is an abstract class and cannot directly create objects, new is its subclass object FruitDaoImpl. // this.getClass() obtains the Class object of FruitDaoImpl // getGenericSuperclass() gets: BaseDao<Fruit> // Type is the top-level interface, representing all types. It has a sub-interface: ParameterizedType ParameterizedType genericSuperclass = (ParameterizedType) this.getClass().getGenericSuperclass(); // Actual: actual // getActualTypeArguments() gets the actual type parameters Type[] actualTypeArguments = genericSuperclass.getActualTypeArguments(); Type actualTypeArgument = actualTypeArguments[0]; // System.out.println(actualTypeArgument.getTypeName());//com.csdn.fruit.pojo.Fruit entityClassName = actualTypeArgument.getTypeName(); loadJdbcProperties(); } //Load jdbc.properties file private void loadJdbcProperties() { try { InputStream inputStream = getClass().getClassLoader().getResourceAsStream("jdbc.properties"); Properties properties = new Properties(); properties.load(inputStream); DRIVER = properties.getProperty("jdbc.driver", "com.mysql.cj.jdbc.Driver"); URL = properties.getProperty("jdbc.url", "jdbc:mysql:///fruitdb"); USER = properties.getProperty("jdbc.user", "root"); PWD = properties.getProperty("jdbc.pwd", "123456"); } catch (IOException e) { throw new RuntimeException(e); } } private Connection getConn() { try { Class.forName(DRIVER); return DriverManager.getConnection(URL, USER, PWD); } catch (ClassNotFoundException | SQLException e) { throw new RuntimeException(e); } } private void close(Connection conn, PreparedStatement psmt, ResultSet rs) { try { if (rs != null) { rs.close(); } if (psmt != null) { psmt.close(); } if (conn != null & amp; & amp; !conn.isClosed()) { conn.close(); } } catch (SQLException e) { throw new RuntimeException(e); } } //Extract execution update method //Perform the update and return the number of affected rows protected int executeUpdate(String sql, Object... params) { PreparedStatement psmt = null; Connection conn = null; try { conn = getConn(); psmt = conn.prepareStatement(sql); setParams(psmt, params); return psmt.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(e); } finally { close(conn, psmt, null); } } //Setting parameters private void setParams(PreparedStatement psmt, Object... params) throws SQLException { if (params != null & amp; & amp; params.length > 0) { for (int i = 0; i < params.length; i + + ) { psmt.setObject(i + 1, params[i]); } } } //Execute the query and return the collection protected List<T> executeQuery(String sql, Object... params) { List<T> list = new ArrayList<>(); Connection conn = null; PreparedStatement psmt = null; ResultSet rs = null; try { conn = getConn(); psmt = conn.prepareStatement(sql); setParams(psmt, params); rs = psmt.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData();//Metadata, structural data of the result set while (rs.next()) { //T t = new T(); T is just a symbol, so it cannot be new T t = (T) ClassUtil.createInstance(entityClassName); int columnCount = rsmd.getColumnCount();//Get the column data of the result set //JDBC all starts from 1, so change i to start from 1 for (int i = 1; i <= columnCount; i + + ) { //Suppose you loop 5 times and get 5 values, which should correspond to the values of 5 attributes of an object. String columnName = rsmd.getColumnLabel(i); Object columnValue = rs.getObject(i); //Assign the columnValue value to the columnName property of the object t ClassUtil.setProperty(t, columnName, columnValue); } list.add(t); } return list; } catch (SQLException e) { throw new RuntimeException(e); } finally { close(conn, psmt, rs); } } protected T load(String sql, Object... params) { Connection conn = null; PreparedStatement psmt = null; ResultSet rs = null; try { conn = getConn(); psmt = conn.prepareStatement(sql); setParams(psmt, params); rs = psmt.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData();//Metadata, structural data of the result set if (rs.next()) { //T t = new T(); T is just a symbol, so it cannot be new T t = (T) ClassUtil.createInstance(entityClassName); int columnCount = rsmd.getColumnCount();//Get the column data of the result set //JDBC all starts from 1, so change i to start from 1 for (int i = 1; i <= columnCount; i + + ) { //Suppose you loop 5 times and get 5 values, which should correspond to the values of 5 attributes of an object. String columnName = rsmd.getColumnLabel(i); Object columnValue = rs.getObject(i); //Assign the columnValue value to the columnName property of the object t ClassUtil.setProperty(t, columnName, columnValue); } return t; } } catch (SQLException e) { throw new RuntimeException(e); } finally { close(conn, psmt, rs); } return null; } }package com.csdn.mymvc.util; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; public class ClassUtil { public static Object createInstance(String entityClassName) { try { return Class.forName(entityClassName).getDeclaredConstructor().newInstance(); } catch (InstantiationException | IllegalAccessException | InvocationTargetException | NoSuchMethodException | ClassNotFoundException e) { throw new RuntimeException(e); } } public static void setProperty(Object instance, String propertyName, Object propertyValue) { Class<?> aClass = instance.getClass(); try { Field field = aClass.getDeclaredField(propertyName); field.setAccessible(true); field.set(instance, propertyValue); } catch (NoSuchFieldException | IllegalAccessException e) { throw new RuntimeException(e); } } }package com.csdn.fruit.dao; import com.csdn.fruit.pojo.Fruit; import java.util.List; //dao: Data Access Object data access object //Interface design public interface FruitDao { void addFruit(Fruit fruit); void delFruit(String fname); void updateFruit(Fruit fruit); List<Fruit> getFruitList(); Fruit getFruitByFname(String fname); }package com.csdn.fruit.dao.impl; import com.csdn.fruit.dao.FruitDao; import com.csdn.fruit.pojo.Fruit; import com.csdn.mymvc.dao.BaseDao; import java.util.List; public class FruitDaoImpl extends BaseDao<Fruit> implements FruitDao { @Override public void addFruit(Fruit fruit) { String sql = "insert into t_fruit values (0,?,?,?,?)"; super.executeUpdate(sql, fruit.getFname(), fruit.getPrice(), fruit.getFcount(), fruit.getRemark()); } @Override public void delFruit(String fname) { String sql = "delete from t_fruit where fname=?"; super.executeUpdate(sql, fname); } @Override public void updateFruit(Fruit fruit) { String sql = "update t_fruit set fcount=? where fname = ?"; super.executeUpdate(sql, fruit.getFcount(), fruit.getFname()); } @Override public List<Fruit> getFruitList() { return super.executeQuery("select * from t_fruit"); } @Override public Fruit getFruitByFname(String fname) { return load("select * from t_fruit where fname = ?", fname); } }package com.csdn.fruit.view; import com.csdn.fruit.dao.FruitDao; import com.csdn.fruit.dao.impl.FruitDaoImpl; import com.csdn.fruit.pojo.Fruit; import java.util.List; import java.util.Scanner; public class Menu { Scanner input = new Scanner(System.in); private FruitDao fruitDao = new FruitDaoImpl(); //Display main menu public int showMainMenu() { System.out.println("================Welcome to the fruit inventory system==================="); System.out.println("1.Display inventory list"); System.out.println("2.Add inventory record"); System.out.println("3. View specific inventory"); System.out.println("4. Fruit removed from shelves"); System.out.println("5.Exit"); System.out.println("============================================ ========="); System.out.print("Please select:"); return input.nextInt(); } //Display inventory list public void showFruitList() { List<Fruit> fruitList = fruitDao.getFruitList(); System.out.println("--------------------------------------------- ----------"); System.out.println("Name\t\tUnit price\t\tInventory\t\tRemarks"); if (fruitList == null || fruitList.size() <= 0) { System.out.println("Sorry, the inventory is empty!"); } else { /* fruitList.forEach(new Consumer<Fruit>() { @Override public void accept(Fruit fruit) { System.out.println(fruit); } });*/ // fruitList.forEach(fruit -> System.out.println(fruit)); fruitList.forEach(System.out::println); } System.out.println("--------------------------------------------- ----------"); } //Add inventory record public void addFruit() { System.out.print("Please enter the name of the fruit:"); String fname = input.next(); Fruit fruit = fruitDao.getFruitByFname(fname); if (fruit == null) { System.out.print("Please enter the fruit unit price:"); Integer price = input.nextInt(); System.out.print("Please enter fruit inventory:"); Integer fcount = input.nextInt(); System.out.print("Please enter fruit remarks:"); String remark = input.next(); fruit = new Fruit(fname, price, fcount, remark); fruitDao.addFruit(fruit); } else { System.out.print("Please enter the additional inventory:"); Integer fcount = input.nextInt(); fruit.setFcount(fruit.getFcount() + fcount); fruitDao.updateFruit(fruit); } System.out.println("Added successfully!"); } //View specific inventory records public void showFruitInfo() { System.out.print("Please enter the name of the fruit:"); String fname = input.next(); Fruit fruit = fruitDao.getFruitByFname(fname); if (fruit == null) { System.out.println("Sorry, no corresponding inventory record found!"); } else { System.out.println("--------------------------------------------- ----------"); System.out.println("Name\t\tUnit price\t\tInventory\t\tRemarks"); System.out.println(fruit); System.out.println("--------------------------------------------- ----------"); } } //Fruits removed from shelves public void delFruit() { System.out.print("Please enter the name of the fruit:"); String fname = input.next(); Fruit fruit = fruitDao.getFruitByFname(fname); if (fruit == null) { System.out.println("Sorry, no inventory records that need to be removed from the shelves were found!"); } else { System.out.print("Are you sure you want to remove it? (Y/N)"); String confirm = input.next(); if ("y".equalsIgnoreCase(confirm)) { fruitDao.delFruit(fname); } } } //quit public boolean exit() { System.out.print("Confirm to exit? (Y/N)"); String confirm = input.next(); boolean flag= !"y".equalsIgnoreCase(confirm); return flag; } }package com.csdn.fruit.view; public class Client { public static void main(String[] args) { Menu m = new Menu(); boolean flag = true; while (flag) { int slt = m.showMainMenu(); switch (slt) { case 1: m.showFruitList(); break; case 2: m.addFruit(); break; case 3: m.showFruitInfo(); break; case 4: m.delFruit(); break; case 5: //Whether a return value is required when designing a method is based on: whether some values need to be left at the place of call for further calculations flag = m.exit(); break; default: System.out.println("You don't play according to the routine!"); break; } } System.out.println("Thank you for using! Goodbye!"); } }package com.csdn.dao.impl; import com.csdn.fruit.dao.FruitDao; import com.csdn.fruit.dao.impl.FruitDaoImpl; import com.csdn.fruit.pojo.Fruit; import org.junit.Test; import java.util.List; public class FruitDaoImplTest { private FruitDao fruitDao = new FruitDaoImpl(); @Test public void testAddFruit() { Fruit fruit = new Fruit("Banana", 7, 77, "Jackfruit is a magical fruit!"); fruitDao.addFruit(fruit); } @Test public void testDelFruit() { fruitDao.delFruit("Hamimelon"); } @Test public void testUpdateFruit() { Fruit fruit = new Fruit("Jackfruit", 5, 1000, "Delicious"); fruitDao.updateFruit(fruit); } @Test public void testGetFruitList() { List<Fruit> fruitList = fruitDao.getFruitList(); fruitList.stream().forEach(System.out::println); } @Test public void testGetFruitByFname() { Fruit fruit = fruitDao.getFruitByFname("Jackfruit"); System.out.println(fruit); } /* //Who is this? This represents the instance object of FruitDaoImpl. Because BaseDao is an abstract class and cannot create objects directly, new is its subclass object FruitDaoImpl. // this.getClass() obtains the Class object of FruitDaoImpl // getGenericSuperclass() gets: BaseDao<Fruit> // Type is the top-level interface, representing all types. It has a sub-interface: ParameterizedType ParameterizedType genericSuperclass = (ParameterizedType) this.getClass().getGenericSuperclass(); // Actual: actual // getActualTypeArguments() gets the actual type parameters Type[] actualTypeArguments = genericSuperclass.getActualTypeArguments(); Type actualTypeArgument = actualTypeArguments[0]; // System.out.println(actualTypeArgument.getTypeName());//com.csdn.fruit.pojo.Fruit entityClassName = actualTypeArgument.getTypeName(); loadJdbcProperties(); */ @Test public void testActualTypeArgument() { //This method is used to test the parameters actually returned by actualTypeArgument } }
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. Java skill treeUsing JDBC to operate databasesJDBC Overview 138,184 people are learning the system