JDBC Learning Notes

1. Database Preparation

To operate the database with JDBC, the first step is of course to create a data table:

CREATE TABLE ' user ' (  ' id ' int (one) ' NOT NULL auto_increment PRIMARY KEY,  ' name ' varchar ' DEFAULT NULL,  ' Birt Hday ' date default null,  ' money ' double default null engine=innodb default Charset=utf8;

2. Basic steps of JDBC Connection database

The JDBC Connection database contains the following basic steps: 1, registration driver, 2, establish connection (Connection), 3, create SQL statement (Statement), 4, execute statement, 5, Process Execution result (ResultSet), 6, release resources.

public static void Test () throws sqlexception{//1. Register driver Class.forName ("Com.mysql.jdbc.Driver");//2. Establish the connection  URL format- JDBC: Sub-Protocol: Sub-name//hostname: Port/database name? Property name = attribute value &. Connection conn = drivermanager.getconnection ("Jdbc:mysql://localhost:3306/jdbc", "Root", "");//3. Create STATEMENT Statement St = Conn.createstatement ();//4. Execute statement ResultSet rs = st.executequery ("SELECT * from user");//5. Processing result while (Rs.next ()) {      System.out.println (Rs.getobject (1) + "\ T" + rs.getobject (2) + "\ T" + rs.getobject (3) + "\ T" + rs.getobject (4));} 6. Release of resources Rs.close (); St.close (); Conn.close ();}

3, simple additions and deletions to check

The second section of the code has a problem, if we throw an exception when executing the code, then the connection cannot be closed, so we should put the close resource operation in finally, so that the database connection resources will be closed anyway. At the same time we will extend the program function, the above example just shows a query operation, the next will show the most commonly used to add, delete, change, check four operations. First, we introduce a jdbcutils class, which encapsulates the first, second, and sixth steps in the database connection step, namely, registering the driver, establishing the connection and releasing the resource operation.

Public final class Jdbcutils {static {try {Class.forName ("Com.mysql.jdbc.Driver");} catch (ClassNotFoundException e) { throw new Exceptionininitializererror (e);}} Private Jdbcutils () {}public static Connection getconnection () throws SQLException {return drivermanager.getconnection ( "Jdbc:mysql://localhost:3306/jdbc", "Root", "" ");  public static void Free (ResultSet rs, Statement St, Connection conn) {try {if (rs! = null) Rs.close ();} catch (SQLException  e) {e.printstacktrace ();} finally {try {if (st! = NULL) St.close ()} catch (SQLException e) {e.printstacktrace ()} finally {if (conn! = null) try {conn.close ();} catch (SQLException e) {e.printstacktrace ()}}}}

You can see that the constructor for this class is a private constructor, so we won't be able to create an instance of this class. In a static initialization domain, we perform a registration-driven operation, and the static initialization domain executes only once when the class is loaded, which guarantees that we will only register the driver once the class is loaded. The getconnection () method then encapsulates the set up connection operation, the free (RS, ST, conn) method encapsulates the release resource operation. Next you can see how to use the Jdbcutils class for adding, deleting, changing, and checking operations:

Add operation void Create () throws SQLException {Connection conn = null; Statement st = null; ResultSet rs = null;try {conn = jdbcutils.getconnection (); st = Conn.createstatement (); int i = St.executeupdate ("in Sert into user (Name,birthday, Money) VALUES (' name1 ', ' 1987-01-01 ', 400) '); System.out.println ("i=" + i);} Finally {Jdbcutils.free (RS, St, conn);}} Delete operation void Delete () throws SQLException {Connection conn = null; Statement st = null; ResultSet rs = null;try {conn = jdbcutils.getconnection (); st = Conn.createstatement (); int i = st.executeupdate ("Delete fro M user where id>4 "); System.out.println ("i=" + i);} Finally {Jdbcutils.free (RS, St, conn);}} Modify operation void Update () throws SQLException {Connection conn = null; Statement st = null; ResultSet rs = null;try {conn = jdbcutils.getconnection (); st = Conn.createstatement (); int i = st.executeupdate ("Update use R set money=money+10 "); System.out.println ("i=" + i);} Finally {Jdbcutils.free (RS, St, conn);}} Query operation Void Read () throws SQLException {ConnectIon conn = null; Statement st = null; ResultSet rs = null;try {conn = jdbcutils.getconnection (); st = Conn.createstatement (); rs = St.executequery ("SELECT ID, nam E, money, birthday from user "), while (Rs.next ()) {System.out.println (Rs.getobject (" id ") +" \ T "+ rs.getobject (" name ") +" \ T "+ rs.getobject (" birthday ") +" \ T "+ rs.getobject (" money "));}}    Finally {Jdbcutils.free (RS, St, conn);}}

4, object-oriented packaging additions and deletions to search

The third section of the example is just to show how to use JDBC for the add-and-remove operation, when actually used in the project, we are not as simple as the above example, Java is object-oriented, so we generally use object-oriented thinking to encapsulate the operation. First of all, in fact, for each data table, we can think of it as an object instance, for example, in this example we define the data table user has Id,name,birthday and money four properties, corresponding we can create the user class as follows:

public class User {private int id;private String name;private Date birthday;private float money;             Getters and Setters}

In accordance with the principle of "interface-oriented programming rather than implementation-oriented programming", we can define the interface for data table operations as follows:

Public interface Userdao {public void AddUser (user user);p ublic User getUser (int userId);p ublic void Update (user user);p UB LIC void Delete (user user);

Then we use JDBC to implement this interface as follows:

public class Userdaojdbcimpl implements Userdao {public void AddUser (user user) {Connection conn = null; PreparedStatement PS = null; ResultSet rs = null;try {conn = Jdbcutils.getconnection (); String sql = "INSERT into user (Name,birthday, Money) VALUES (?,?,?)"; PS = conn.preparestatement (sql);p s.setstring (1, User.getname ());p s.setdate (2, New Java.sql.Date (User.getbirthday (). GetTime ()));p s.setfloat (3, User.getmoney ());p s.executeupdate (); catch (SQLException e) {throw new RuntimeException (E.getmessage (), e);} finally {Jdbcutils.free (RS, PS, conn);}} public void Delete (user user) {Connection conn = null; Statement st = null; ResultSet rs = null;try {conn = jdbcutils.getconnection (); st = Conn.createstatement (); String sql = "Delete from user where id=" + User.getid (); st.executeupdate (SQL);} catch (SQLException e) {throw new RuntimeException (E.getmessage (), e);} finally {Jdbcutils.free (RS, St, conn);}} Public User getUser (int userId) {Connection conn = null; PreparedStatement PS = null; ResultSet Rs = null; User User = null;try {conn = Jdbcutils.getconnection (); String sql = "SELECT ID, Name, money, birthday from user where id=?"; PS = conn.preparestatement (sql);p s.setint (1, userId); rs = Ps.executequery (); while (Rs.next ()) {user = new user (); User.setid (Rs.getint ("id")), User.setname (rs.getstring ("name")), User.setmoney (Rs.getfloat ("money")); User.setbirthday (rs.getdate ("Birthday"));}} catch (SQLException e) {throw new RuntimeException (E.getmessage (), e);} finally {Jdbcutils.free (RS, PS, conn);} return user;} public void update (user user) {Connection conn = null; PreparedStatement PS = null; ResultSet rs = null;try {conn = Jdbcutils.getconnection (); String sql = "Update user set name=?", birthday=?, money=? where id=? ";p s = conn.preparestatement (sql);p s.setstring (1, User.getname ());p s.setdate (2, New Java.sql.Date (User.getbirthday ( ). GetTime ());p s.setfloat (3, User.getmoney ());p S.setint (4, User.getid ());p s.executeupdate (); catch (SQLException e) {throw new RuntimeException (E.getmessage (), E);} Finally {Jdbcutils.free (RS, PS, conn);}}}

You can see that the real core code is very similar to the second section of the code, but the code written in this style is more extensible, if one day we do not intend to use JDBC, and instead of using Hibernate to connect to the database, the use of interface programming only need to modify the implementation, without modifying the other parts, greatly reducing the difficulty of the modification.

5. Incoming SQL execution

It should be noted that the above code uses the PreparedStatement object, Preparestatement is a precompiled statement object, it will be created when the general framework of SQL set up, some variables are represented by placeholders, when used, We then set the values for these placeholders. Preparestatement The biggest feature is to prevent SQL injection, more secure, so you need to splice user input scenes, we recommend the use of preparestatement.

public class Userdaoutils {private Userdaoutils () {}static User executeQuery (String sql, object[] params) throws Sqlexcept ion {Connection conn = null; PreparedStatement PS = null; ResultSet rs = null; User User = null;try {conn = Jdbcutils.getconnection ();p s = conn.preparestatement (sql); for (int i = 1; I <= Params.leng Th i++) {Ps.setobject (I, params[i-1]);} rs = Ps.executequery (), while (Rs.next ()) {user = new user (), User.setid (Rs.getint ("id")), User.setbirthday (Rs.getdate (" Birthday ")); User.setmoney (Rs.getfloat (" money ")); User.setname (rs.getstring (" name "));}} Finally {Jdbcutils.free (RS, PS, conn);} return user;} static int executeupdate (String sql, object[] params) throws SQLException {Connection conn = null; PreparedStatement PS = Null;int rs = 0;try {conn = Jdbcutils.getconnection ();p s = conn.preparestatement (sql); for (int i = 1; I <= params.length; i++) {Ps.setobject (I, params[i-1]);} rs = Ps.executeupdate ();} finally {Jdbcutils.free (NULL, PS, conn);} return RS;}} public class UserdaojDBCIMPL2 implements userdao{@Overridepublic void AddUser (user user) {try {userdaoutils.executeupdate ("INSERT INTO User" ( Name,birthday, Money) VALUES (?,?,?) ", New Object[]{user.getname (), User.getbirthday (), User.getmoney ()}); catch (SQLException e) {e.printstacktrace ();}} @Overridepublic user getUser (int userId) {User user = null;try {user = Userdaoutils.executequery ("Select ID, name, Money, Birthday from user where id=? ", New Object[]{userid}); catch (SQLException e) {e.printstacktrace ();} return user;} @Overridepublic void Update (user user) {try {userdaoutils.executeupdate ("Update user set name=?, birthday=?, money=? Where id=? ", New Object[]{user.getname (), User.getbirthday (), User.getmoney (), User.getid ()}); catch (SQLException e) {e.printstacktrace ();}} @Overridepublic void Delete (user user) {try {userdaoutils.executeupdate ("delete from User where id=?", New Object[]{user. GetId ()});} catch (SQLException e) {e.printstacktrace ();}}}

First we define a Userdaoutils object that contains two methods, namely execute　 The Query () and Executeupdate () methods, both of which contain two parameters, are the parameters of the SQL statement and the SQL statement. We then defined the USERDAOJDBCIMPL2 class, which uses Userdaoutils to implement the Userdao interface, which is much simpler than Userdaojdbcimpl.

6. Encapsulating objects with result set metadata

The above UserDaoJdbcImpl2 and userdaoutils codes are already very concise, but there is a problem, if we want to encapsulate the JDBC operation of other objects, then we will have to redefine a pair of utils and Impl, this is actually repetitive labor, So do we have any way to avoid these repetitive tasks? Impl objects must be defined, because we need to implement different objects, and if we want to define fewer objects, we can only not define Utils objects. Looking at the Exectuequery () and Executeupdate () methods of userutils, it is found that only the ExecuteQuery () method is coupled to the user object, and the coupling part only encapsulates the part of the result set, We can abstract this part of the code into an interface that allows the caller to pass in so that this part of the coupling can be avoided, so the interface is defined as follows:

Public interface RowMapper {public Object Maprow (ResultSet rs) throws SQLException;}

We then modify the fourth section of the Userdaoutils object as follows and rename it to Myjdbctemplate:

public class Myjdbctemplate {private Myjdbctemplate () {}public static Object ExecuteQuery (String sql, object[] args, RowMapper rowmapper) {Connection conn = null; PreparedStatement PS = null; ResultSet rs = null;try {conn = Jdbcutils.getconnection ();p s = conn.preparestatement (sql); for (int i = 0; i < Args.leng Th i++) Ps.setobject (i + 1, args[i]), rs = Ps.executequery (); Object obj = Null;if (Rs.next ()) {obj = Rowmapper.maprow (rs);} return obj;} catch (SQLException e) {throw new RuntimeException (E.getmessage (), e);} finally {Jdbcutils.free (RS, PS, conn);}} public static int executeupdate (String sql, object[] params) throws SQLException {Connection conn = null; PreparedStatement PS = Null;int rs = 0;try {conn = Jdbcutils.getconnection ();p s = conn.preparestatement (sql); for (int i = 1; I <= params.length; i++) {Ps.setobject (I, params[i-1]);} rs = Ps.executeupdate ();} finally {Jdbcutils.free (NULL, PS, conn);} return RS;}} 

you see, Now that our ExecuteQuery () method has been decoupled from the user object, the entire object has been decoupled from the user object and is a common method that we can use to implement the Userdao interface as follows:

public class UserDaoJdbcImpl3 implements Userdao {@Overridepublic void AddUser (user user) {try { Myjdbctemplate.executeupdate ("INSERT into User (Name,birthday, Money) VALUES (?,?,?)", new object[] {user.getname (), User.getbirthday (), User.getmoney ()}); catch (SQLException e) {e.printstacktrace ();}} @Overridepublic user getUser (int userId) {User user = null;try {user = (user) myjdbctemplate.executequery ("Select ID, Name  , money, birthday from user where id=? ", new object[] {userId}, new RowMapper () {@Overridepublic Object maprow (ResultSet RS) throws SQLException {User user = new User (); User.setid (Rs.getint ("id")); User.setname (rs.getstring ("name")); User.setmoney (Rs.getfloat ("Money")), User.setbirthday (Rs.getdate ("Birthday"); return user;}}); catch (Exception e) {e.printstacktrace ();} return user;} @Overridepublic void Update (user user) {try {myjdbctemplate.executeupdate ("Update user set name=?, birthday=?, money=? Where id=? ", new object[] {user.getname (), User.getbirthday (), User.getmoney (), User.getid ()});} catch (SQLException e) {e.printstacktrace ();}} @Overridepublic void Delete (user user) {try {myjdbctemplate.executeupdate ("delete from User where id=?", new object[] {use R.getid ()});} catch (SQLException e) {e.printstacktrace ();}}}

The implementation of USERDAOJDBCIMPL3 is very similar to the implementation of section Fourth USERDAOJDBCIMPL2, only the GetUser () method differs from USERDAOJDBCIMPL2, in UserDaoJdbcImpl3 total, Not only do we pass SQL statements and SQL parameters, we also need to pass the RowMapper object, which helps us encapsulate the query results into a single User object.

7, with the configuration file implementation and specific classes of decoupling

We've been talking about the different implementations of Userdao, but we haven't talked about how to use these implementations, and the simplest way to create them is to create the objects first, like this:

Userdao Userdao = new Userdaojdbcimpl ();

But this implementation hard-coded into the code is not very elegant, if we want to modify the implementation, we have to recompile the code, better we use the configuration file definition implementation class, the creation of the read configuration file determines which implementation should be used. The format of the configuration file uses the Java properties format, and the contents of the configuration file are as follows:

Userdaoclass=cn.test.userdaojdbcimpl3

We will use the Factory mode to create a Daofactory object that has a Createuserdao () method that will read back the implementation of a Userdao interface, the implementation of the method, we can choose to create a new return each time, you can also choose to create the first time and then, Cached, and then directly return to the cached object method, where we choose the second, the implementation of the object is as follows:

public class Daofactory {private static Userdao Userdao = Null;private static Daofactory instance = new Daofactory ();p Riva Te daofactory () {}public static daofactory getinstance () {return instance;} Public Userdao Createuserdao () {if (Userdao = = null) {try {Properties prop = new Properties (); InputStream instream = Daofa Ctory.class.getClassLoader (). getResourceAsStream ("Daoconfig.properties");p rop.load (instream); String Userdaoclass = Prop.getproperty ("Userdaoclass"); Class<?> clazz = Class.forName (userdaoclass); Userdao = (Userdao) clazz.newinstance ();} catch (Throwable e) {throw new Exceptionininitializererror (e);}} return Userdao;}}

Finally, write a userdaotest class that simply tests the above code:

public class Userdaotest {public static void main (string[] args) {Userdao Userdao = Daofactory.getinstance (). Createuserda O (); User user = new user (), User.setbirthday (New Date ()), User.setmoney (234242), User.setname ("xxxx"), Userdao.adduser (user ); User u = userdao.getuser (1); System.out.println (U.getid () + "\ T" + u.getname () + "\ T" + u.getmoney () + "\ T" + u.getbirthday ());}}

JDBC Learning Notes