JDBC implements Common addition, deletion, modification, and query basic methods, and jdbc adds and deletes

JDBC implements Common addition, deletion, modification, and query basic methods, and jdbc adds and deletes

There are many data management frameworks in java, such as hibernate and mybatis. But I first learned JDBC. I think JDBC is still very good, it gives me a deeper understanding of data operations. Today I will write the JDBC basic class I have written again! Deepen your memory !!!

First, paste the BaseDAO class of the general addition query implementation class.

Package com. shude. DAO; import java. lang. reflect. field; import java. lang. reflect. method; import java. lang. reflect. parameterizedType; import java. lang. reflect. type; import java. SQL. connection; import java. SQL. databaseMetaData; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. SQLException; import java. util. arrayList; import java. util. linkedHashMap; import java. util. list; import java. ut Il. map; import java. util. map. entry; import com. shude. DAO. im. IBaseDAO; import com. shude. util. configUtil; import com. shude. util. jdbcUtil; import com. shude. util. pageUtil; /*** common addition, deletion, modification, and query implementation class * @ author Administrator ** @ param <E> */public class BaseDAO <E> implements IBaseDAO <E> {protected static ConfigUtil configUtil; protected Class <?> Cls;/*** get package name, Class name */public BaseDAO () {Class <?> ClsTemp = this. getClass (); Type type = clsTemp. getGenericSuperclass (); if (type instanceof ParameterizedType) {Type [] types = (ParameterizedType) type ). getActualTypeArguments (); cls = (Class <?>) Types [0] ;}}/*** get the table name in the database of the corresponding class */static {configUtil = ConfigUtil. newInstance ("/tabORM. properties ");}/*** save Method */public boolean saveInfo (E e) {boolean flag = true; try {Class <?> Cls = e. getClass (); // obtain the table name String tableName = configUtil. getVal (cls. getName (); // obtain the primary key String prykey = getPrimKey (tableName); // record the data column List <String> filedList = new ArrayList <String> (); // obtain the SQL statement String SQL = getsavesql (tableName, prykey, filedList); // execute sqlflag = excuteSQL (SQL, e, filedList);} catch (Exception e1) {flag = false; e1.printStackTrace ();} return flag;}/*** modification method */public void modifyInfo (E e) {Class <?> Cls = e. getClass (); // obtain the table name String tableName = configUtil. getVal (cls. getName (); // obtain the primary key String prykey = getPrimKey (tableName); // record the data column List <String> filedList = new ArrayList <String> (); // obtain the SQL statement String SQL = getmodifysql (tableName, prykey, filedList); // Add the primary key to the set filedList. add (prykey); // execute sqlexcuteSQL (SQL, e, filedList);}/*** Delete Method */public void deleteInfo (Object id) {// obtain the table name String tableName = configUtil. getVal (cl S. getName (); // obtain the primary key String prykey = getPrimKey (tableName ); // obtain the SQL statement String SQL = "update" + tableName + "set status = '1' where" + prykey + "=? "; Connection conn = null; PreparedStatement pstm = null; try {conn = JdbcUtil. getConn (); pstm = conn. prepareStatement (SQL); pstm. setObject (1, id1_1_p0000.exe cute ();} catch (Exception e) {e. printStackTrace ();} finally {JdbcUtil. closeConn (conn) ;}}/*** query all methods */public void queryinfo (PageUtil <E> pageUtil) {E e = pageUtil. getEntity (); // obtain the table name String tableName = configUtil. getVal (cls. getName (); // obtain the query condition Map <String, Object> paramMap = getParamMap (e); // obtain sqlString SQL = getquerySQL (paramMap, tableName); SQL + = "limit ?,? "; ParamMap. put ("pageSize", (pageUtil. getPageSize ()-1) * pageUtil. getPageNum (); paramMap. put ("pageNum", pageUtil. getPageNum (); // execute SQLexcutQuery (pageUtil, SQL, paramMap, tableName);}/*** single query method */public E queryById (Object id) {// obtain the table name String tableName = configUtil. getVal (cls. getName (); // obtain the primary key String prykey = getPrimKey (tableName ); // obtain sqlString SQL = "select * from" + tableName + "where 1 = 1 and" + prykey +" =? "; // Run SQLConnection conn = null; PreparedStatement pstm = null; ResultSet rs = null; E e = null; try {conn = JdbcUtil. getConn (); pstm = conn. prepareStatement (SQL); pstm. setObject (1, id); rs = p0000.exe cuteQuery (); List <E> list = getEntityList (rs); e = list. get (0);} catch (Exception ex) {ex. printStackTrace ();} finally {JdbcUtil. closeConn (conn);} return e;}/*** get the total number of entries * @ param paramMap * @ param tableName * @ retur N */private Integer getPagenumsss (Map <String, Object> paramMap, String tableName) {paramMap. remove ("pageSize"); paramMap. remove ("pageNum"); String SQL = getquerySQL (paramMap, tableName); SQL = "select count (*) from (" + SQL + ") tempTab "; connection conn = null; PreparedStatement pstm = null; ResultSet rs = null; Integer pagenumsss = 0; try {conn = JdbcUtil. getConn (); pstm = conn. prepareStatement (SQL); int I = 1; for (Entry <String, Object> entry: paramMap. entrySet () {Object val = entry. getValue (); if (val instanceof java. lang. string) {pstm. setString (I, "%" + val. toString () + "%");} else if (val instanceof java. lang. integer) {pstm. setInt (I, Integer. parseInt (val. toString ();} I ++;} rs = p0000.exe cuteQuery (); while (rs. next () {pagenumsss = rs. getInt (1) ;}} catch (Exception e) {e. printStackTrace ();} finally {JdbcUtil. close Conn (conn);} return pagenumsss;}/*** obtain query SQL * @ param paramMap * @ param tableName * @ return */private String getquerySQL (Map <String, object> paramMap, String tableName) {StringBuffer SQL = new StringBuffer (); SQL. append ("select * from "). append (tableName ). append ("where 1 = 1 and status = '0'"); List <String> columlist = getTableColumns (tableName); for (Entry <String, Object> entry: paramMap. entrySet ()) {String columName = entry. getKey (); for (String colnName: columlist) {if (colnName. repeated signorecase (columName) {if (entry. getValue () instanceof java. lang. string) {SQL. append ("and "). append (columName ). append ("like? ");} Else {SQL. append (" and "). append (columName). append (" =? ") ;}Break ;}} return SQL. toString ();}/*** obtain the query condition * @ param e * @ return */private Map <String, Object> getParamMap (E e) {Map <String, object> paramMap = new LinkedHashMap <String, Object> (); Field [] fields = e. getClass (). getDeclaredFields (); for (Field field: fields) {try {field. setAccessible (true); Object val = field. get (e); if (val! = Null &&! "". Equals (val. toString () {paramMap. put (field. getName (), val) ;}} catch (Exception e1) {e1.printStackTrace () ;}} return paramMap ;} /*** obtain the primary key * @ param tableName * @ return */private String getPrimKey (String tableName) {Connection conn = null; DatabaseMetaData metaData = null; ResultSet rs = null; string primKeyName = null; try {conn = JdbcUtil. getConn (); metaData = conn. getMetaData (); rs = metaData. getPrimary Keys (conn. getCatalog (), null, tableName. toUpperCase (); while (rs. next () {primKeyName = rs. getString ("COLUMN_NAME") ;}} catch (SQLException e) {e. printStackTrace ();} finally {JdbcUtil. closeConn (conn);} return primKeyName ;} /*** Save the method and execute SQL * @ param SQL * @ param e * @ param filedList * @ return */private boolean excuteSQL (String SQL, E entity, list <String> filedList) {boolean flag = true; Connection conn = Null; PreparedStatement pstm = null; try {conn = JdbcUtil. getConn (); pstm = conn. prepareStatement (SQL); // value int I = 1; for (String columName: filedList) {Object val = getFieldValue (entity, columName); pstm. setObject (I, val1_polici1_000000000000p0000.exe cute ();} catch (SQLException e1) {e1.printStackTrace (); flag = false;} finally {JdbcUtil. closeConn (conn);} return flag;}/*** obtain the SQL statement of the modification method * @ param tableName * @ param prykey * @ Param filedList * @ return */private String getmodifysql (String tableName, String prykey, List <String> filedList) {StringBuffer SQL = new StringBuffer (); SQL. append ("update "). append (tableName ). append ("set"); List <String> columnList = getTableColumns (tableName); for (String columnName: columnList) {if (! ColumnName. inclusignorecase (prykey) {filedList. add (columnName); SQL. append (columnName). append ("= ?, ") ;}} If (SQL. toString (). endsWith (",") {SQL = new StringBuffer (SQL. substring (0, SQL. length ()-1);} SQL. append ("where "). append (prykey ). append ("=? "); Return SQL. toString ();} /*** run the query all SQL statements * @ param pageUtil * @ param SQL * @ param paramMap * @ param tableName */private void excutQuery (PageUtil <E> pageUtil, String SQL, map <String, Object> paramMap, String tableName) {Connection conn = null; PreparedStatement pstm = null; ResultSet rs = null; try {conn = JdbcUtil. getConn (); pstm = conn. prepareStatement (SQL); int I = 1; for (Entry <String, Object> entry: ParamMap. entrySet () {Object val = entry. getValue (); if (val instanceof java. lang. string) {pstm. setString (I, "%" + val. toString () + "%");} else if (val instanceof java. lang. integer) {pstm. setInt (I, Integer. parseInt (val. toString ();} I ++;} rs = p0000.exe cuteQuery (); List <E> list = getEntityList (rs); // encapsulate the query result pageUtil. setList (list); // encapsulate the total number of pageUtil. setPageNumSum (getPagenumsss (paramMap, tableName);} catch (Ti On e) {e. printStackTrace ();} finally {JdbcUtil. closeConn (conn) ;}}/*** get table attributes * @ param entity * @ param columName * @ return */private Object getFieldValue (E entity, String columName) {Class <?> Cls = entity. getClass (); Object value = null; // obtain all member attributes in the class Field [] fields = cls. getDeclaredFields (); for (Field field: fields) {// obtain the attribute name String fieldName = field. getName (); // determines whether the attribute name is the same as the column name if (fieldName. equalsIgnoreCase (columName) {// obtain the method name String methodName = "get" + fieldName according to the rule. substring (0, 1 ). toUpperCase () + fieldName. substring (1); try {// obtain Method object method = cls by Method name. getMethod (methodName); // executes the method and returns the result. Value = method. invoke (entity);} catch (Exception e) {e. printStackTrace ();} break;} return value ;} /*** Save the SQL statement * @ param tableName * @ param prykey * @ param filedList * @ return */private String getsavesql (String tableName, String prykey, list <String> filedList) {StringBuffer SQL = new StringBuffer (); SQL. append ("insert "). append (tableName ). append ("("); List <String> columnList = getTableColumns (TableName); for (String string: columnList) {if (! String. repeated signorecase (prykey) {SQL. append (string ). append (","); filedList. add (string) ;}} if (SQL. toString (). endsWith (",") {SQL = new StringBuffer (SQL. substring (0, SQL. length ()-1);} SQL. append (") value ("); for (int I = 0; I <filedList. size (); I ++) {SQL. append ("?, ");} If (SQL. toString (). endsWith (",") {SQL = new StringBuffer (SQL. substring (0, SQL. length ()-1);} SQL. append (")"); return SQL. toString ();}/*** obtain the table column * @ param tableName * @ return */private List <String> getTableColumns (String tableName) {List <String> columnList = new ArrayList <String> (); Connection conn = null; DatabaseMetaData metaData = null; ResultSet rs = null; conn = JdbcUtil. getConn (); try {metaData = conn. getMetaData (); rs = metaData. getColumns (conn. getCatalog (), null, tableName. toUpperCase (), null); while (rs. next () {String clumnName = rs. getString ("COLUMN_NAME"); columnList. add (clumnName) ;}} catch (SQLException e) {e. printStackTrace ();} finally {JdbcUtil. closeConn (conn);} return columnList;}/*** encapsulate the query result * @ param rs * @ return * @ throws Exception */@ SuppressWarnings ("unchecked ") private List <E> getEntityList (ResultSet rs) throws Exception {List <E> list = new ArrayList <E> (); Field [] fields = cls. getDeclaredFields (); while (rs. next () {E e = (E) cls. newInstance (); for (Field field: fields) {try {field. setAccessible (true); String columName = field. getName (); String fieldType = field. getType (). getSimpleName (); if ("String ". equals (fieldType) {field. set (e, rs. getString (columName);} else if ("Integer ". equals (fieldType) {field. set (e, rs. getInt (columName) ;}} catch (Exception e1) {e1.printStackTrace () ;}} list. add (e) ;}return list ;}}

Mysql configuration file config. properties

driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/dbsda10?characterEncoding=UTF-8 username=root password=rootroot temfile=C:\\ image=C:\\images 

ConfigUtil

Package com. shude. util; import java. io. IOException; import java. util. properties;/*** read the configuration file * @ author Administrator **/public class ConfigUtil {private static ConfigUtil configUtil; private static final String DEFALT_FILE_PATH = "/config. properties "; private static String name; private Properties pop; private ConfigUtil () {init ();} private void init () {pop = new Properties (); try {if (name! = Null) pop. load (ConfigUtil. class. getResourceAsStream (name); pop. load (ConfigUtil. class. getResourceAsStream (DEFALT_FILE_PATH);} catch (IOException e) {e. printStackTrace () ;}} public static ConfigUtil newInstance (String name) {ConfigUtil. name = name; if (configUtil = null) configUtil = new ConfigUtil (); return configUtil ;} /*** get the value on the right of the configuration file * @ param key * @ return */public String getVal (String key) {return pop. getProperty (key );}}

Before that, the condition is that the field name of the database must correspond to and be the same as the name in the object class. The configuration file related to the table name and object class name is as follows:

TabORM. properties

com.shude.entity.UserInfo=user_info com.shude.entity.RoleInfo=role_info com.shude.entity.FabricInfo=fabric_info com.shude.entity.ProductInfo=product_info com.shude.entity.MateInfo=mate_info com.shude.entity.ProgramInfo=program_info 

The above JDBC Implementation of the General addition, deletion, modification, and query of the basic class method is all the content shared to you by xiaobian, I hope to give you a reference, but also hope you can support a lot of help homes.