A summary of the original ecological JDBC problem

Package Com.js.ai.modules.aiyq.testf;import Java.sql.connection;import Java.sql.drivermanager;import Java.sql.preparedstatement;import Java.sql.resultset;import Java.sql.sqlexception;public class JdbcTest {public           static void Main (string[] args) {//database connection Connection Connection = null;   Precompiled statement, using pre-compiled statement to improve database performance PreparedStatement PreparedStatement = null;      Result set ResultSet ResultSet = null;                  try {//Load Database driver Class.forName ("Com.mysql.jdbc.Driver"); Get the database link through the drive management class connection = Drivermanager.getconnection ("jdbc:mysql://localhost:3306/mybatis?characterencoding=                   Utf-8 "," root "," root ");        Define SQL statements? represents a placeholder String sql = "SELECT * from t_user where username =?";      Get preprocessing statement preparedstatement = connection.preparestatement (sql);              Set the parameter, the first parameter is the ordinal of the parameter in the SQL statement (starting at 1), the second parameter is the parameter value set preparedstatement.setstring (1, "Lee"); Issuing SQL execution queries to the database, querying the result set ResultSet = Preparedstatement.executequerY (); Iterate through the query result set while (Resultset.next ()) {System.out.println (resultset.getstring ("id") + "" + resultset.getstring (  "username"));           }} catch (Exception e) {e.printstacktrace ();} finally {//frees resource if (resultSet! = null) {try {       Resultset.close (); } catch (SQLException e) {//TODO auto-generated catch block E.       Printstacktrace ();      }} if (PreparedStatement! = null) {try {preparedstatement.close ();                        } catch (SQLException e) {//TODO auto-generated catch block     E.printstacktrace ();     }} if (connection! = null) {try {connection.close (); } catch (SQLException e) {//TODO auto-generated catch block E.prin     Tstacktrace (); }  }}}}

　　

The problem of the above code is summarized as follows:

1, the database connection, the use of the creation, do not use immediate release, the database is frequently connected to open and close, resulting in a waste of database resources, affecting database performance.

Solution: Use a database connection pool to manage database connections.

2, hard code SQL statements into the Java code, if the SQL statement modification, the need to recompile Java code, not conducive to system maintenance.

Solution: Configure the SQL statement in the XML configuration file, and do not need to recompile the Java code, even if SQL changes.

3, set the parameters to the PreparedStatement, the placeholder position and set the parameter value, hard coding in the Java code, not conducive to system maintenance.

Solution: Configure SQL statements and placeholder symbols and parameters all in XML.

4, when traversing the result set data from the Resutset, there is hard coding, and the field of getting table is hard-coded, which is not conducive to system maintenance.

Solution: Automatically map the result set of the query to a Java object.

A summary of the original ecological JDBC problem