DAY22------JDBC Connection in the Java language

DAY22------JDBC Connection in the Java language

First, JDBC Overview:

"JDBC" is the Java connection database, according to the different database established connection also has a certain difference. We are primarily working with data on MySQL databases. The JDBC architecture consists of two main types: the JDBC Application layer and the JDBC driver layer. The following table shows the JDBC connection to various database diagrams. Here's a concrete look at the relationship between Java and the database.

650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/18/345899113279f4e3a46276d60b5ea6b2.png "title=" Jdbcapi.png "alt=" 345899113279f4e3a46276d60b5ea6b2.png "/>

Second, JDBC:

Before you start learning JDBC Specifically, prepare for it. Download the appropriate jar package (Mysql-connector-java-5.1.24-bin.jar) on the MySQL database website, set up a test database in MySQL database, and an EMP data table to prepare the data table accordingly.

1. The basic steps of using JDBC to connect to a database:

In the Java language, the connection to our team database uses JDBC technology, which is generally divided into the following steps.

1.1. Load driver: Use Class.forName ("...");

1.2, establish the connection: use Drivermanager.getconnection ("...");

1.3. Create the SQL statement object. Createstatement ()

1.4. The method of using SQL object to fetch the corresponding SQL statement execution

Specific examples:

Connect to a database using JDBC Package www.com.c1;import java.sql.connection;import java.sql.drivermanager;import  java.sql.resultset;import java.sql.sqlexception;import java.sql.statement;public class  jdbc01 {/** 1, Com.mysql.jdbc.Driver. Load driver, this is a fixed syntax that can be changed according to the different database. * 2, jdbc:mysql://. Similar to Protocol * 3, localhost:3306/. Install the local host and port number of the database. You can also change the IP address and port number * 4, test. Database name * 5,?,&. Represents the connection between the parameters that are taken, and the parameters. * 6, user. What is the user name of the database? * 7, password. What is the password for the database? *///connection mode A private static final string url01 =  "Jdbc:mysql://localhost:3306/test? user=root&password=123456 ";     //connection mode two     /*private static  final string url02 = "Jdbc:mysql://localhost:3306/test";         private static string user = "Root";         private static String password =  "123456";     *///Create a connected object private static connection connection;public static void  main (String[] args)  {try {//1, load Drive Class.forName ("Com.mysql.jdbc.Driver");//2, establish connection, mode one. and create a Connection object. Connection = drivermanager.getconnection (URL01);/*//establish connection, mode two. Drivermanager.getconnection (Url02, user, password); *///3, create SQL statement object. Statement statement = connection.createstatement ();//The execution of a query statement. (1), only the SQL statement that executes the query, and uses the ResultSet result set to receive all the results resultset resultset = statement.executequery the stored query (" Select * from emp "),//(2), traverse result set while  (Resultset.next ())  {//output result set System.out.println (" ID: "+resultset.getint" ("id") + ";" + "User name:" +resultset.getstring ("username") + ";" + "Password:" +resultset.getstring ("password");} Second, execute the UPDATE statement. (1) Update the data. Note that when using the update operation, the return value is of type int, that is, whether a row of data is affected Int updatecount = statement.executeupdate ("update emp  set password=888888 where id=2 ");//(2) Determine if the update is successful.         if  (updatecount>0)  {system.out.println ("Update succeeded! ");}  else {system.out.println ("Update failed! ");} Third, execute arbitrary SQL statements. Return value: Note The return type is a Boolean type and is mainly divided into two types.           //generally works best when creating a table or creating a database.     //returns True when the SELECT statement    //return false: Execute INSERT, UPDATE, DELETE, or create statement   The    //if not exists keyword is to determine if the table exists and does not create a     boolean create if it exists  = statement.execute ("Create table if not exists test (id int  Primary key,name varchar ());if  (create = true)  {     SYSTEM.OUT.PRINTLN ("created successfully! ");}  else {system.out.println ("Create failed! ");}}  catch  (classnotfoundexception e)  {e.printstacktrace ();}  catch  (sqlexception e)  {e.printstacktrace ();}     }}

2, through the JDBC Connection technology, realizes the "Dynamic SQL" Operation data table.

Specific examples:

Using dynamic SQL query data in JDBC package www.com.c1;import java.sql.connection;import java.sql.drivermanager; Import java.sql.preparedstatement;import java.sql.resultset;import java.sql.sqlexception;public  class jdbc02 {//defining connection Database characters private static final string url =           "jdbc:mysql://localhost:3306/test";//define character receive connection data private  Static connection connection;public static void main (String[] args)  {try  {//1, Load Drive Class.forName ("Com.mysql.jdbc.Driver");//2, Establish connection connection =  Drivermanager.getconnection (url,  "root",  "123456");//3, compiling dynamic sqlpreparedstatement ps =  Connection.preparestatement ("Select * from emp where id=? and username="); /4, assume that the user passes the ID and username two parameters from the page,     //and queries the data as the query criteria. ★★★★int id = 2; string username =  "LS";//5, ID and user passedName is copied to dynamic SQL? , as a condition Ps.setint (1, id);p s.setstring (2, username);//NOTE: When this step is copied, 1 represents the first question mark, 2 is the second question mark,//question mark is from the beginning of 1, not 0./ /6, using PS call ExecuteQuery () method,//And the result set of the query to receive, ResultSet represents the result set resultset resultset = ps.executequery (); /7, output query to the result while  (Resultset.next ())  {    system.out.println ("ID:" + Resultset.getint ("id"));     system.out.println ("Username:" +         resultset.getstring ("username"));}}  catch  (classnotfoundexception e)  {e.printstacktrace ();}  catch  (sqlexception e) { e.printstacktrace ();}}}

3. Use the properties file and encapsulate the SQL execution method to manipulate the data table.

Properties file: It is to write some unchanged code into a file, by the transfer of this file directly can be connected. At the same time, when changing the database information, you only need to change the properties file. You do not need to change the specified code.

Specific examples:

(1), create a property file.

Create a new file in the project's "SRC" level directory. The new-->other--> input text file can be created. Writes related properties to this file

#连接jdbc的属性文件 Driver = com.mysql.jdbc.Driver URL = jdbc:mysql://localhost:3306/test Username = Root Password = 123456

Named: Jdbc.properties file.

     (2), creating the Jdbcmethod class. Used to encapsulate the appropriate method. and connect the corresponding properties file.

package www.com.c2;import java.sql.connection;import java.sql.drivermanager;import  java.sql.preparedstatement;import java.sql.resultset;import java.sql.sqlexception;import  java.sql.statement;import java.util.resourcebundle;//is primarily used to encapsulate all methods that use dynamic SQL. As well as the database properties file connection//Note: We have already written various properties in the properties file Jdbc.properties, we only need to apply. PUBLIC&NBSP;CLASS&NBSP;JDBCMETHOD&NBSP;{//1, using constructors to establish a database connection://    in the Jdbctest class is the object that created the class,//     we can make a connection directly from the constructor, because the constructor executes the code at the moment the object is created. &NBSP;&NBSP;3) Definition conn receive connection Data Private connection connection;public jdbcmethod () {//  1) Load Properties File Resourcebundle bundle = resourcebundle.getbundle ("jdbc"); String driver = bundle.getstring ("Driver"); string url = bundle.getstring ("url"); String user = bundle.getstring ("username"); String password = bundle.getstring ("password"); try {//2) load Driver Class.forName (driver);//4) Establish connection   connection = Drivermanager.getconnection (Url, user, password);}  catch  (classnotfoundexception e)  {e.printstacktrace ();}  catch  (sqlexception e)  {e.printstacktrace ();}} ---------------------------------------------------///2, write dynamic sql// 1), package implementation INSERT, UPDATE, delete operation public  int excuteupdate (string sql) {statement statement;int count = 0;try {/ /(1): Create SQL Object Statement = connection.createstatement ();//(2): Execute UPDATE statement count =  Statement.executeupdate (SQL);}  catch  (sqlexception e)  {e.printstacktrace ();} Return count;} &NBSP;2), Package implementation query statement Public resultset excutequery (STRING&NBSP;SQL) {statement statement; resultset rs = null;try {//(1): Create SQL Object Statement = connection.createstatement (); /(2): Executes the related query statement rs = statement.executequery (SQL);}  catch  (sqlexception e)  {e.printstacktrace ();} Return rs;} 3, the use of multiple question marks to encapsulate the dynamic SQL searchAsk "★★★★★"/** *  @param  sql: A query that needs to be executed, with a question mark or without a question mark.  *  @param &nbsp, ..... : Represents the number of non-predictable question marks.  *  @param  params: Parameters required in SQL  *  @return Execute query  */public ResultSet  Excutequery (string sql , object ... params ) {preparedstatement  PreparedStatement; resultset resultset = null;try {//the method of executing SQL,preparedstatement =  Connection.preparestatement (SQL);//determine if the SQL statement is with?. if  (params != null && params.length > 0) {//If any, assign a value to the question mark for  ( int i = 0; i < params.length; i++)  {preparedstatement.setobject (i+1  , params[i] );}} Resultset = preparedstatement.executequery ();}  catch  (sqlexception e)  {e.printstacktrace ();} Return resultset;}}

(3), create a new Jdbctest class with the main function.

Package Www.com.c2;import java.sql.resultset;//The main way to extract encapsulated dynamic SQL, public class Jdbctest {public static void main (string[] args) {Jdbcmethod Jdbcmethod = new Jdbcmethod ();//create the appropriate SQL statement to manipulate the data table. int inser = Jdbcmethod.excuteupdate ("INSERT into EMP values ()"), int update = jdbcmethod.excuteupdate ("Update emp set wher E "); int delete = Jdbcmethod.excuteupdate (" Delete from emp where ... ");//2, Execute query statement, return result set resultSet ResultSet = Jdbcmethod.excutequery ("select* from EMP where");}}

Iv. concluding remarks

Here we put some of the common JDBC methods are finished, remember, the connection database does not need to change the table data additions and deletions. Be sure to master the method of using dynamic SQL to manipulate databases and ideas.

This article from the "Program Ape" blog, reproduced please contact the author!

Day22------JDBC Connection in the Java language