(17) JDBC (Java Data base Connectivity,java database connection) Base usage

OneIntroduction to JDBC Related Concepts1.1Introduction to JDBC

In order to simplify and unify the operation of database, Sun Company defines a set of Java Operation Database Specification (interface), called JDBC. This interface is implemented by the database vendor, so that developers can manipulate the database by simply learning the JDBC interface and loading the specific driver through JDBC.

As shown in the following:

Ii. writing a JDBC program

2.1 Setting up the experimental environment

A. Start the MySQL database, that is, turn on Mysql/bin/mysqld.exe.

B. Create the database and create the required tables. such as (when creating databases and tables, set their encoding to utf-8 encoding or later prone to error):

2.2 Create a Web project and import the database driver package.

2.3 Write JavaBean, that is, the PO object is also called persisted object, this object's member property corresponds to the database column one by one, in this case the database and the username and passwd two columns, the PO object should also have these two member attributes.

Userpo.java

Package Po;public class Userpo {private string Username;private string Passwd;public string GetUserName () {return userName ;} public void Setusername (String userName) {this.username = UserName;} Public String getpasswd () {return passWd;} public void setpasswd (String passWd) {this.passwd = PassWd;}}

Parse: The member property in the JavaBean object should be private and must have the get () and set () methods.

2.4 Create DAO package, specifically for manipulating data (that is, interacting with the database, making the difference between the deletions), generally have interface classes and implementation classes.

• Userpoi.java

Package Dao;import java.util.list;import Po. Userpo;public interface Userpoi {public boolean addUser (Userpo user);p ublic list<userpo>  gets ();

• Userpoimpl.java

Package Dao;import Java.sql.connection;import Java.sql.drivermanager;import java.sql.resultset;import Java.sql.statement;import java.util.arraylist;import java.util.list;import Po. Userpo;public class Userpoimpl implements Userpoi {public boolean addUser (Userpo user) {Boolean flag=false; Connection Conn=null; Statement Stat=null;try {//Load Database driverClass.forName ("Com.mysql.jdbc.Driver");//Get a link to the database from the driveConn=drivermanager.getconnection ("Jdbc:mysql://127.0.0.1:3306/user?useunicode=true&characterencoding=utf-8 "," Root "," ");//Create conditional statement object, which is an object that writes SQL statements in the databasestat= conn.createstatement ();//Execute SQL statements and get the number of records that are executed successfully using the Executeupdate () method if the operation is deleted or not, and if the check operation gets the ResultSet return valueint Res=stat.executeupdate ("INSERT into user values ('" +user.getusername () + "', '" +user.getpasswd () + "')"), if (res!=0) {//If res is not equal to 0, the SQL statement execution succeeds. Flag=true;}} catch (Exception e) {e.printstacktrace ();} Finally{try{if (Conn!=null &&!conn.isclosed ()) {conn.close ();} if (Stat!=null && stat.isclosed ()) {stat.close ();}} catch (Exception e) {e.printstacktrace ();}} return flag;} Public list<userpo> gets () {list<userpo> list=new arraylist<userpo> (); Connection Conn=null; Statement Stat=null; ResultSet res=null;try {class.forname ("Com.mysql.jdbc.Driver"); Conn=drivermanager.getconnection ("jdbc:mysql:// 127.0.0.1:3306/user?useunicode=true&characterencoding=utf-8 "," Root "," ""); Stat=conn.createstatement (); res= Stat.executequery ("SELECT * from User"), while (Res.next ()) {//cannot be ifUserpo user=new Userpo (); Note that the location of New Userpo () here cannot be placed outside new otherwise the list has only one object User.setusername (res.getstring ("UserName")); USER.SETPASSWD ( Res.getstring ("PassWd")); List.add (user);}} catch (Exception e) {e.printstacktrace ();} Finally{try{if (Conn!=null &&!conn.isclosed ()) {conn.close ();} if (Stat!=null && stat.isclosed ()) {stat.close ();} if (Res!=null && res.isclosed ()) {res.close ();}} catch (Exception e) {e.printstacktrace ();}} return list;}}

2.5 Create a test class to test.

• Usertest.java

Package Test;import java.util.list;import org.junit.test;import dao.userpoimpl;import po. Userpo;public class Usertest {@Testpublic void Userpotest () {Userpoimpl im=new userpoimpl (); Userpo user=new Userpo () user.setusername ("Zhang San"); USER.SETPASSWD ("123"); Im.adduser (user); @Testpublic void Userpoget () {Userpoimpl im=new userpoimpl (); List<userpo> list=im.gets (); for (int i=0;i<list.size (); i++) System.out.println (List.get (i). GetUserName ()) ;}}

Results:

Third, attention

• The URL is used to identify the location of the database and tells the JDBC program which database to connect to by the URL address:

• Common database URL address notation:

• Oracle notation: Jdbc:oracle:thin: @localhost: 1521:sid
• SQL Server notation: jdbc:microsoft:sqlserver://localhost:1433; Databasename=sid
• MySQL notation: jdbc:mysql://localhost:3306/sid

• Statement class explanation

The statement object in the JDBC program is used to send SQL statements to the database, statement object common methods:

• ExecuteQuery (String sql): Used to send query statements to data.
• Executeupdate (String sql): Used to send INSERT, UPDATE, or DELETE statements to the database
• Execute (String SQL): Used to send arbitrary SQL statements to the database
• Addbatch (String sql): puts multiple SQL statements into one batch.
• ExecuteBatch (): Sends a batch of SQL statement execution to the database.

• ResultSet class explanation

The resultset in the JDBC program is used to represent the execution result of the SQL statement. A table-like approach is used when the resultset encapsulates the execution result.　　The ResultSet object maintains a cursor to the table data row, and initially, the cursor calls the Resultset.next () method before the first row, allowing the cursor to point to a specific row of data and invoke the method to fetch the row's data. ResultSet since it is used to encapsulate execution results, the object provides a get method for getting data: Gets any type of data getObject (int index) GetObject (string columnName) Gets the data of the specified type , for example: getString (int index) getString (String columnName) ResultSet also provides a way to scroll the result set:

• Next (): Move to the next line
• Previous (): Move to previous line
• Absolute (int row): Move to the specified line
• Beforefirst (): Moves the front of the resultset.
• Afterlast (): Moves to the last face of the resultset.

　　

• Freeing resources

After the JDBC program runs, remember to release the objects that were created by the program to interact with the database during the run, typically resultset, statement, and connection objects, especially connection objects, which are very rare resources, Must be released immediately after use, if the connection can not be timely, correct shutdown, it is very easy to cause system downtime.　　Connection's use principle is to create as late as possible, releasing as early as possible. To ensure that the resource release code can run, the resource release code must also be placed in the finally statement.

(17) JDBC (Java Data base Connectivity,java database connection) Base usage