General Jdbc-demo

1. JDBC Toolkit (Utils): Includes methods for getting database connections, shutting down database resources, etc.

Jdbctools_pro.java

Package Com.app.utils;import Java.beans.propertyvetoexception;import Java.io.ioexception;import Java.sql.connection;import Java.sql.resultset;import Java.sql.sqlexception;import Java.sql.Statement;import Java.util.properties;import javax.sql.datasource;import com.mchange.v2.c3p0.combopooleddatasource;/** * JDBC Tool class * * This includes methods such as getting a database connection, shutting down database resources, and so on.  */public class Jdbctools_pro {private static Jdbctools_pro instance; public Combopooleddatasource ds; private static String c3p0properties = "jdbc.properties";//COMMIT Transaction for database transaction public static void commit (Connection Connection) {if (connection! = null) {try {connection.commit ()} catch (SQLException e) {e.printstacktrace ();}}} ROLLBACK TRANSACTION public static void Rollback (Connection Connection) {if (Connection! = null) {try {connection.rollback ();} catch ( SQLException e) {e.printstacktrace ();}}} Start transaction public static void Begintx (Connection Connection) {if (Connection! = null) {try {Connection.setautocommit (FALSE)} catch (SQLException e) {e.printstacktrace ();}}} Private static DataSource DataSource = null;//database connection pool should be initialized only once. /*static{datasource = new Combopooleddatasource ("Helloc3p0");} public static Connection getconnection () throws Exception {return datasource.getconnection ();  */public Jdbctools_pro () throws Exception {Properties P = new Properties ();  /* * Under the same package * *////P.load (This.getclass (). getClassLoader (). getResourceAsStream (c3p0properties));    /* * Root directory */p.load (This.getclass (). getClassLoader (). getResourceAsStream (c3p0properties));    ds = new Combopooleddatasource ();  Ds.setuser (P.getproperty ("Jdbc.username"));  Ds.setpassword (P.getproperty ("Jdbc.password"));  Ds.setjdbcurl (P.getproperty ("Jdbc.url"));    Ds.setdriverclass (P.getproperty ("Jdbc.driverclassname"));  Ds.setminpoolsize (Integer.parseint (P.getproperty ("c3p0.minpoolsize"));  Ds.setmaxpoolsize (Integer.parseint (P.getproperty ("c3p0.maxpoolsize"));  Ds.setmaxidletime (Integer.parseint (P.getproperty ("C3p0.maxidletime")); Ds.setacquireincrement (Integer.parseint (p.getpropErty ("C3p0.acquireincrement"));    Ds.setmaxstatements (Integer.parseint (P.getproperty ("c3p0.maxstatements"));  Ds.setmaxstatementsperconnection (Integer.parseint (P.getproperty ("c3p0.maxstatementsperconnection"));  Ds.setidleconnectiontestperiod (Integer.parseint (P.getproperty ("C3p0.idleconnectiontestperiod")); Ds.setacquireretryattempts (Integer.parseint (P.getproperty ("c3p0.acquireretryattempts"));}   public static final Jdbctools_pro getinstance () {if (instance = = null) {try {instance = new Jdbctools_pro ();   } catch (Exception e) {e.printstacktrace (); }} return instance;  Public synchronized final Connection getconnection () {try {return ds.getconnection ();  } catch (SQLException e) {e.printstacktrace (); } return null;  }public static void Releasedb (ResultSet ResultSet, Statement statement,connection Connection) {if (ResultSet! = null) {try {Resultset.close ();} catch (SQLException e) {e.printstacktrace ();}} if (statement! = NULL) {try {statement.close ();} CAtch (SQLException e) {e.printstacktrace ();}} if (connection! = NULL) {The connection object of the try {//Database connection pool is close when//is not actually closed, but the database connection is returned to the database connection pool. Connection.close ();} cat CH (SQLException e) {e.printstacktrace ();}}}}

2. JDBC Database underlying implementation (DAO)

2.1.1. The underlying implementation

Basedaoimpl.java

Package Com.app.dao.impl;import Java.sql.connection;import Java.sql.sqlexception;import java.util.List;import Org.apache.commons.dbutils.queryrunner;import Org.apache.commons.dbutils.handlers.beanhandler;import Org.apache.commons.dbutils.handlers.beanlisthandler;import Org.apache.commons.dbutils.handlers.ScalarHandler; Import com.app.dao.basedao;import com.app.utils.reflectionutils;/** * Use Queryrunner to provide its specific implementation * @param <t>:  The generic type that the subclass needs to pass in. */public class Basedaoimpl<t> implements basedao<t> {private Queryrunner Queryrunner = Null;private class <T> type;public Basedaoimpl () {queryrunner = new Queryrunner (); type = Reflectionutils.getsupergenerictype ( GetClass ());} @Overridepublic void Batch (Connection Connection, String sql, object[] ... args) throws SQLException {Queryrunner.batch ( connection, SQL, args);} @SuppressWarnings ("unchecked") @Overridepublic <E> E getforvalue (Connection Connection, String sql, Object ...) args) throws SQLException {return (E) queryrunner.queRY (Connection, SQL, New Scalarhandler (), args);} @Override public list<t> getforlist (Connection Connection, String sql, Object ... args) throws SQLException {return q Ueryrunner.query (connection, SQL, new beanlisthandler<> (type), args);} @Overridepublic T Get (Connection Connection, String sql, Object ... args) throws SQLException {return Queryrunner.query (CO Nnection, SQL, New beanhandler<> (type), args);} @Overridepublic void Update (Connection Connection, String sql, Object ... args) throws SQLException {queryrunner.update ( connection, SQL, args);}}

2.1.2. Physical class Inheritance

Userdaoimpl.java

Package Com.app.dao.impl;import Java.sql.connection;import Com.app.dao.userdao;import com.app.entity.User;import Com.app.utils.jdbctools_xml;public class Userdaoimpl extends basedaoimpl<user> implements userdao{@ Overridepublic Boolean isuserexisted (String ID) {Connection conn = Null;boolean Flage = false;try {conn = jdbctools_xml.ge Tconnection (); String sql = "SELECT ID from the users where userId =?"; String p = getforvalue (conn, SQL, ID); if (null!=p) {flage = true;}} catch (Exception e) {//TODO auto-generated catch Blocke.printstacktrace ();} finally {jdbctools_xml.releasedb (null, NULL, conn);} return flage;} @Overridepublic Boolean isuserpass (string ID, string password) {Connection conn = Null;boolean Flage = false;try {conn = J Dbctools_xml.getconnection (); String sql = "SELECT ID from the users where id =?" and password =? "; String p = getforvalue (conn, SQL, ID); if (null!=p) {flage = true;}} catch (Exception e) {//TODO auto-generated catch Blocke.printstacktrace ();} finally {Jdbctools_xml.relEasedb (NULL, NULL, conn);} return flage;} @Overridepublic long GetNumber () {Long num = 0; Connection conn = null;try {conn = Jdbctools_xml.getconnection (); String sql = "SELECT COUNT (*) from users"; num = getforvalue (conn, SQL, NULL);} catch (Exception e) {e.printstacktrace ();} finally {jdbctools_xml.releasedb (null, NULL, conn);} return num;} @Overridepublic User Getuserbyid (String ID) {Connection conn = null; User User = null;try {conn = Jdbctools_xml.getconnection (); String sql = "Select Userid,username,password" + "from users where userId =?"; user = Get (conn,sql,id);} catch (Exception e) {e.printstacktrace ();} Finally{jdbctools_xml.releasedb (NULL, NULL, conn);} return user;} @Overridepublic void Insertuser (String userId, String userName, string password) {Connection conn = null;try {conn = JDBCT Ools_xml.getconnection (); String sql = "INSERT into users VALUES (?,?,?)"; UPDATE (conn, SQL, Userid,username,password);} catch (Exception e) {e.printstacktrace ();} finally {jdbctools_xml.releasedb (null, NULL, conn);}} @Overridepublic void DeleteUser (String id) {Connection conn = null;try {conn = Jdbctools_xml.getconnection (); String sql = "DELETE from users where userId =?"; UPDATE (conn, SQL, id);} catch (Exception e) {e.printstacktrace ();} finally {jdbctools_xml.releasedb (null, NULL, conn);}} @Overridepublic void UpdateUser (String userId, String userName, string password) {Connection conn = null;try {conn = JDBCT Ools_xml.getconnection (); String sql = "UPDATE users set userName =?, Password =?" where userId =? "; UPDATE (conn, SQL, Username,password,userid);} catch (Exception e) {e.printstacktrace ();} finally {jdbctools_xml.releasedb (null, NULL, conn);}}}

2.2.1

Basedao.java

Package Com.app.dao;import java.sql.connection;import java.sql.sqlexception;import java.util.list;/** * DAO interface for accessing data. C0/>* defines the various ways to access a data table * @param the type of entity class that the T:dao handles.  */public interface basedao<t> {void Batch (Connection Connection, String sql, Object [] ... args) throws SQLException ;<e> E getforvalue (Connection connection,string sql, Object ... args) throws SQLException; List<t> getforlist (Connection connection,string sql, Object ... args) throws SQLException; T Get (Connection Connection, String sql, Object ... args) throws sqlexception;void update (Connection Connection, String sq L, Object ... args) throws SQLException;}

2.2.2

Userdao.java

Package Com.app.dao;import Com.app.entity.user;public Interface Userdao extends Basedao<user>{boolean isuserexisted (String id), Boolean isuserpass (string id,string password), long GetNumber (); User Getuserbyid (string id), void UpdateUser (String Userid, String Username, string password), void DeleteUser (string id); void Insertuser (String Userid, String Username, string password);}

　　

3. JDBC Physical Class (entity)

User.java

Package Com.app.entity;public class User {private string Userid;private string Username;private string Password;public Us ER () {//TODO auto-generated constructor stub}public User (String userId, String userName, string password) {super (); this.u Serid = userid;this.username = Username;this.password = password;} Public String GetUserId () {return userId;} public void Setuserid (String userId) {this.userid = userId;} Public String GetUserName () {return userName;} public void Setusername (String userName) {this.username = UserName;} Public String GetPassword () {return password;} public void SetPassword (String password) {this.password = password;}  @Overridepublic String toString () {return "user [userid=" + UserId + ", username=" + UserName + ", password=" + password + "]";}}

　　

General Jdbc-demo