Java database tools and java database tools

Java database tools and java database tools

Previously, I used to write a project using a framework and encapsulated entities very conveniently. This time I wrote it directly using servlet and jsp, encapsulated data myself, a lot of repeated code, and I just reviewed the reflection mechanism, so I wrote a tool class myself.

1. Fields in the team table

2. entity Team class

package com.beb.entity;public class Team {    private Integer id;    private String teamId;    private String teamName;    private String teamDesc;            public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getTeamId() {        return teamId;    }    public void setTeamId(String teamId) {        this.teamId = teamId;    }    public String getTeamName() {        return teamName;    }    public void setTeamName(String teamName) {        this.teamName = teamName;    }    public String getTeamDesc() {        return teamDesc;    }    public void setTeamDesc(String teamDesc) {        this.teamDesc = teamDesc;    }    @Override    public String toString() {        return "Team [id="+id+", teamId=" + teamId + ", teamName=" + teamName                + ", teamDesc=" + teamDesc + "]";    }    public Team(String teamId, String teamName, String teamDesc) {        this.teamId = teamId;        this.teamName = teamName;        this.teamDesc = teamDesc;    }    public Team() {        }        }

3. Connect to the database's property file db. propertie and place it under the src path.

driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/rssuser=rootpassword=123456

4. Tool class SqlHelper. java

Package com. beb. util; import java. io. fileNotFoundException; import java. io. IOException; import java. io. inputStream; import java. lang. reflect. field; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. resultSetMetaData; import java. SQL. SQLException; import java. util. arrayList; import java. util. list; import java. util. properties; p Ublic class SqlHelper {/* parameters used to connect to the database */private static String driver; private static String url; private static String user; private static String password; /* database parameter */private static Connection conn = null; private static PreparedStatement ps = null; private static ResultSet rs = null; static {Properties prop = new Properties (); try {// obtain the path of the property file InputStream is = SqlHelper. class. getClassLoader (). getResourc EAsStream ("db. properties "); // InputStream is = new FileInputStream (new File (" db. properties "); prop. load (is); driver = prop. getProperty ("driver"); url = prop. getProperty ("url"); user = prop. getProperty ("user"); password = prop. getProperty ("password"); Class. forName (driver);} catch (FileNotFoundException e) {e. printStackTrace ();} catch (IOException e) {e. printStackTrace ();} catch (ClassNotFoundExcept Ion e) {e. printStackTrace () ;}}/*** get database Connection * @ return */public Connection getConnection () {try {conn = DriverManager. getConnection (url, user, password);} catch (SQLException e) {e. printStackTrace ();} return conn;}/*** close resource * @ param conn * @ param ps * @ param rs */public void close (Connection conn, PreparedStatement ps, resultSet rs) {if (conn! = Null) {try {conn. close (); conn = null;} catch (SQLException e) {e. printStackTrace () ;}} if (ps! = Null) {try {ps. close (); ps = null;} catch (SQLException e) {e. printStackTrace () ;}} if (rs! = Null) {try {rs. close (); rs = null;} catch (SQLException e) {e. printStackTrace () ;}}/ *** common query. During encapsulation, * @ param SQL * @ param parameters * @ return */public List query (String SQL, object [] parameters) {List list = new ArrayList (); try {conn = getConnection (); ps = conn. prepareStatement (SQL); // pass the parameter if (parameters! = Null & parameters. length> 0) {for (int j = 0; j <parameters. length; j ++) {ps. setObject (j + 1, parameters [j]) ;}} rs = ps.exe cuteQuery (); // get the number of columns, getMetaData () Get the data source, int columnCount = rs. getMetaData (). getColumnCount (); while (rs. next () {// encapsulate the obtained data into the Object array [] obj = new Object [columnCount]; for (int I = 0; I <columnCount; I ++) {obj [I] = rs. getObject (I + 1);} list. add (obj) ;}} catch (SQLException e) {e. pri NtStackTrace () ;}finally {close (conn, ps, rs) ;}return list ;}/ *** generic query, suitable for all entity, however, the field name of the table must be the same as the attribute name in the Object class * @ param <T> * @ param entity * @ param SQL * @ param parameters * @ return */public <T extends Object> List <T> query2 (T entity, string SQL, Object... parameters) {List <T> list = new ArrayList <T> (); try {Connection conn = getConnection (); ps = conn. prepareStatement (SQL); // pass the parameter if (parameters! = Null & parameters. length> 0) {for (int j = 0; j <parameters. length; j ++) {ps. setObject (j + 1, parameters [j]) ;}} rs = ps.exe cuteQuery (); // obtain the data source ResultSetMetaData rsmd = rs. getMetaData (); // get the number of columns int columnCount = rsmd. getColumnCount (); // obtain the column name String [] columnName = new String [columnCount]; for (int I = 0; I <columnCount; I ++) {columnName [I] = rsmd. getColumnName (I + 1);} // obtain the entity Class Object, Class clazz = entit Y. getClass (); // obtain all attributes Field [] fields = clazz. getDeclaredFields (); Object obj = null; while (rs. next () {obj = clazz. newInstance (); for (int I = 0; I <columnCount; I ++) {for (int j = 0; j <fields. length; j ++) {// determines whether the table column name is the same as the attribute name if (columnName [I]. equals (fields [j]. getName () {fields [j]. setAccessible (true); fields [j]. set (obj, rs. getObject (columnName [I]); break ;}}} list. add (T) obj) ;}} catch (SQLException e) {e. PrintStackTrace ();} catch (InstantiationException e) {e. printStackTrace ();} catch (IllegalAccessException e) {e. printStackTrace () ;}finally {close (conn, ps, rs) ;}return list ;} /*** add, delete, modify, and @ param SQL * @ param parameters * @ return */public int update (String SQL, Object [] parameters) {try {conn = getConnection (); conn. setAutoCommit (false); ps = conn. prepareStatement (SQL); if (parameters! = Null) {for (int I = 0; I <parameters. length; I ++) {ps. setObject (I + 1, parameters [I]) ;}} int res = ps.exe cuteUpdate (); conn. commit (); return res;} catch (SQLException e) {e. printStackTrace (); return 0 ;}finally {close (conn, ps, rs );}}}

5. Test. java

package com.beb.test;import java.util.List;import com.beb.entity.Team;import com.beb.util.SqlHelper;public class Test {    public static void main(String[] args) {        SqlHelper sqlHelper = new SqlHelper();        String sql = "select * from team";        List<Team> teamList = sqlHelper.query2(new Team(), sql, null);        for(Team team:teamList){            System.out.println(team);        }    }}

6. Running result