JAVA Database Programming (JDBC technology)-getting started

This cainiao is involved in Java. I am not in a rush to look at the basic syntax, the features of some Java versions, or some promotion knowledge, because there is one thing. net (OOP) programming ideas, so you can find and learn these Java syntaxes and what they are used. I can't wait to use JAVA to add, delete, modify, and query databases. To achieve this, let's take a look at how Java operates the database. net, you may configure the web. config, Or you can write some DBhelper classes and then call some of the methods defined by yourself to add, delete, modify, and query, or you can configure some data controls and so on, and often find that operations are basically integrated and simple. What about Java?

JDBC technology

Baidu Introduction: JDBC (Java Data Base Connectivity, java database connection) is a Java API used to execute SQL statements. It can provide unified access to multiple relational databases, it consists of a group of classes and interfaces written in Java. JDBC provides a benchmark to build more advanced tools and interfaces so that database developers can write database applications. JDBC does not directly access the database. You need to use the JDBC driver provided by the database vendor.

Common JDBC classes and interfaces can be used for programming and development. These classes and interfaces can be used for convenient data access and processing. These classes and interfaces are located in the java. sal package. The details are as follows ~~

After a bunch of nonsense, you can go to Baidu to find all these nonsense. You can find that Baidu is very powerful, sharp, friendly, and invincible ~~~~ Far away .... Next we will start the JDBC journey.

Database Connection

To access the database in Java, first load a database driver. The database driver only needs to be loaded once during the first access. Then, a Connection instance is created every time you access the database to obtain the database Connection. This allows you to execute SQL statements that operate the database. The database connection is released after use.

Database driver

Different databases implement different JDBC interfaces, so different database driver packages are generated. The driver package contains some classes responsible for database connection, and passes the SQL statements that we want to operate. Because my PC at work uses SQL2012, so we are going to http://www.microsoft.com/zh-cn/search/DownloadResults.aspx here? Q = download the following driver from jdbc:

Some people may say why this is not the 3.0 or 2.0 driver! Because my SQL version is 2012, download this. If you are 2008 or 2005, you can download the following 3.0 driver package! If you don't understand this, click here:

Download the appropriate SQL driver!

Load database drivers

The method for loading the database driver in Java is to call the static method forName () of the Class, which is written as follows:

Class.forName(String driveManager);

The forName () method parameter is used to specify the database driver to be loaded. If the load is successful, the driver class will be loaded and registered to DriveManager. If the load fails, a ClassNotFoundExecption exception will be thrown.

There is no need to gossip: In order to reject program errors as much as possible, I don't want to make a bunch of errors when I use Java JDBC for the first time, then, when scholars use tools, they make mistakes. Because I am operating on the local SQL 12 database of my PC, I have prepared everything to use the default port. So we need to start SQL network configuration protocol TCP/IP to start, of course, it is not necessary to start it here if it has already started!

Connect to the SQL2012 Database

Load the driver we just loaded into MyEclipse as follows:

Cute stuff, like a milk bottle o (^ yellow ^) o.

Next we will start the data connection operation of SQL2012:

Package myJava. jdbc; import java. SQL. *; public class SelectQuery {Connection conn; // create a public Connection getConnection () {try {Class. forName ("com. microsoft. sqlserver. jdbc. SQLServerDriver "); System. out. println ("database driver loaded successfully"); conn = DriverManager. getConnection ("jdbc: sqlserver: // localhost: 1433; DatabaseName = DB_ShowData", "sa", "123456"); if (conn! = Null) {System. out. println ("database connection succeeded") ;}} catch (Exception e) {// TODO Auto-generated catch blocke. printStackTrace () ;}// return the Connection object return conn;}/*** @ param args */public static void main (String [] args) {// TODO Auto-generated method stub SelectQuery getcon = new SelectQuery (); getcon. getConnection ();}}

The running result is as follows:

It should be a good trip to see this! There was no exception, and then we checked the connection successfully. The code for a simple query is as follows:

Package myJava. jdbc; import java. SQL. *; public class SelectQuery {Connection conn; // create a public Connection getConnection () {try {Class. forName ("com. microsoft. sqlserver. jdbc. SQLServerDriver "); System. out. println ("database driver loaded successfully"); conn = DriverManager. getConnection ("jdbc: sqlserver: // localhost: 1433; DatabaseName = DB_ShowData", "sa", "123456"); if (conn! = Null) {System. out. println ("database connection succeeded") ;}} catch (Exception e) {// TODO Auto-generated catch blocke. printStackTrace () ;}// return the Connection object return conn;}/*** @ param args */public static void main (String [] args) {// TODO Auto-generated method stub SelectQuery getcon = new SelectQuery (); getcon. getConnection (); // query String SQL = "SELECT * FROM [dbo]. [User] "; try {PreparedStatement Ps = getcon. conn. prepareStatement (SQL); ResultSet Rs = Ps.exe cuteQuery (); while (Rs. next () {String name = Rs. getString ("Name"); String password = Rs. getString ("Password"); System. out. println (name); System. out. println (password);} Rs. close (); Ps. close ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();}}}

The running result is as follows:

As a result, o (^) o is not the focus. The focus is on the classes and interfaces that we use in the JDBC technology in this query, maybe you have read the above Code and are confused. I am talking about people who are getting started with me, so after the following explanation, you will understand it!

First, we use the DriverManager class in getConnection.

The DriverManager class is used to manage all drivers in the database. It is the management layer of JDBC and acts as a connection between users and drivers and between database drivers. The methods in the DriverManager class are static methods, so you do not need to instantiate them when using them. You can directly call the class name.

The typical DriverManager class method is as follows:

Method	Function
GetConnection (String url, String user, String password)	Specify three parameters: url of the database to be connected, database user name, and database password.
SetLoginTimeout ()	Obtain the maximum waiting time when the driver attempts to log on to a database.
PrintIn (String Message)	Print a message to the current JDBC log stream

 

 

 

Next, we can see the Connection interface.

The Connection interface indicates the Connection to a specific database. To operate data in a data table, you must first obtain the database connection.

Common Methods for Connection interfaces are as follows:

Method	Function
CreateStatement ()	Create a Statement object
CreateStatement (int resultSetType, int resultSetConcurrency)	Creates a Statement object that produces a ResultSet object with a given type, concurrency, and storage.
PrepareStatement ()	Create a preprocessing object PreparedStatement
IsReadOnly ()	Check whether the Read mode of the current Connection object is read.
SetReadOnly ()	Sets the read/write mode of the current Connection object. The default mode is non-read-only.
Commit ()	Make all changes made after the last commit and rollback become persistent changes
Roolback ()	Cancel all changes made in the current transaction and release all database locks currently held by this Connection object
Close ()	Release the Connection object database and JDBC resources immediately

 

 

 

 

 

 

 

 

 

PreparedStatement Interface

The PreparedStatement interface inherits the Statemetn interface, which is used to execute dynamic SQL statements. By executing SQL statements through the PreparedStatement instance, the statements are pre-compiled and saved to the PreparedStatement instance, so that the SQL statements can be executed repeatedly. You can use the prepareStatement () method of the Connection class to obtain the PreparedStatement object. Common Methods of the PreparedStatement interface:

Method	Function Description
Execute ()	Execute an SQL statement in this PreparedStatement object. This statement can be of any type.
ExecuteQuery ()	The SQL query statement in this PreparedStatement object. The returned result is the ResultSet object of the query result set.
ExecuteUpadte ()	Execute an SQL statement in this PreparedStatement object. The SQL statement must be an INSET, UPDATE, or DELETE statement, or a DDl statement that does not return a value.
SetByte (int pIndex, byte bt)	Set the position of the pIndex parameter to the specified byte parameter value bt.
SetDouble (int pIndex, double dou)	Set the position of the pIndex parameter to the given double parameter value dou.
SetInt (int pInde, int x)	Set the position of the pIndex parameter to the given int parameter value x.
SetObject (int pIndex, Object o)	Set the position of the pIndex parameter to the specified Object parameter value o
SetString (int pIndex, String str)	Set the position of the pIndex parameter to the given String parameter value str

 

 

 

 

 

 

 

 

 

 

 

ResultSet Interface

The ResultSet interface is similar to a data table used to temporarily store the result set obtained by the database query operation. The ResultSet instance points to the current data row. The starting position of the pointer is prior to the first record of the query result set. When you obtain the query result set, you can use the next () method to move the pointer down. If the next row exists, the return value is true; otherwise, the return value is false.

Method	Function
GetInt ()	Obtains the specified value of the current row of the ResultSet object in the form of intt.
GetFloat ()	Obtains the specified value of the current row of the ResultSet object in float form.
GetDate ()	Obtains the specified value of the current row of the ResultSet object as Data.
GetBoolean ()	Obtains the specified value of the current row of the ResultSet object in boolean format.
GetString ()	Obtains the specified value of the current row of the ResultSet object in String format.
GetObject ()	Obtains the specified value of the current row of the ResultSet Object as an Object.
Frist ()	Move the pointer to the first row of the current record
Last ()	Move the pointer to the last row of the current record
Next ()	Move the pointer down a row
BeforeFirst ()	Move the pointer to the beginning of the Set
AfterLast ()	Move the pointer to the end of the Set
Absolute (int index)	Returns the row with the number specified by ResultSet.
InFist ()	Determines whether the pointer is located in the first row of the current ResultSet set.
IsLast ()	Determines whether the pointer is located in the last row of the current ResultSet set.
UpdateInt ()	Update a specified column with the specified int Value
UpadateFloat ()	Update a specified column with the specified float Value
UpdateLong ()	Update a specified column with the specified Long value
UpdateString ()	Update a specified column with the String value
UpdateObject ()	Update a specified column with the Object Value
UpdateNull ()	Change the value of the specified column to NULL.
UpdateDate ()	Update a specified column with the specified Date value
UpdateDouble ()	Update a specified column with the specified double value
GetRow ()	View the current index number
InsertRow ()	Insert row content to the database
UpdateRow ()	Synchronize the content of the current row to the database
DeleteRow ()	Delete the current row, but it is not synchronized to the database. After the close () method is executed, it is synchronized to the database.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After the basic explanation is complete, you can check the information and find out what is not specific or wrong. Then, we will continue to write an additional method:

Package myJava. jdbc; import java. SQL. *; public class SelectQuery {Connection conn; // create a public Connection getConnection () {try {Class. forName ("com. microsoft. sqlserver. jdbc. SQLServerDriver "); System. out. println ("database driver loaded successfully"); conn = DriverManager. getConnection ("jdbc: sqlserver: // localhost: 1433; DatabaseName = DB_ShowData", "sa", "123456"); if (conn! = Null) {System. out. println ("database connection succeeded") ;}} catch (Exception e) {// TODO Auto-generated catch blocke. printStackTrace () ;}// return the Connection object return conn;}/*** @ param args */public static void main (String [] args) {// TODO Auto-generated method stub SelectQuery getcon = new SelectQuery (); getcon. getConnection (); // query/** String SQL = "SELECT * FROM [dbo]. [User] "; try {PreparedStatement Ps = getcon. conn. prepareStatement (SQL); ResultSet Rs = Ps.exe cuteQuery (); while (Rs. next () {String name = Rs. getString ("Name"); String password = Rs. getString ("Password"); System. out. println (name); System. out. println (password);} Rs. close (); Ps. close ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} ** // Add try {Statement statement = getcon. conn. createStatement (); int count = statement.exe cuteUpdate ("insert into [dbo]. [User] VALUES ('superman ', '000000') "); getcon. conn. close (); System. out. println ("the number of added rows is" + count);} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();}}}

Let's take a look at the running result:

OK! The result is not important. The emphasis is to add the Statement interface in the method.

Statement instances are used to send SQL statements to the database based on established connections. This interface is used to execute static SQL statements. Common methods for the Statement interface are as follows:

Method	Function
Execute (String SQL)	Execute a static SELECT statement. This statement may return previous results.
ExecuteQuery (String SQL)	Execute the given SQL statement and return the ResultSet object.
ClearBatch ()	Clear the current SQL command list of this Statement object
ExecuteBatch ()	Submit a batch of commands to the database for execution. If all commands are successfully executed, the array of the update count group is returned.
ExecuteUpdate ()	Execute the given SQL statement, which can be INSERT, UPDATE, or DELETE.
AddBatch (String SQL)	Add the given SQL command to the current command LIST OF THE Satement object.
Close ()	Releases the database and JDBC resources occupied by the Statement instance.

 

 

 

 

 

 

 

 

 

 

 

Package myJava. jdbc; import java. SQL. *; public class SelectQuery {Connection conn; // create a public Connection getConnection () {try {Class. forName ("com. microsoft. sqlserver. jdbc. SQLServerDriver "); System. out. println ("database driver loaded successfully"); conn = DriverManager. getConnection ("jdbc: sqlserver: // localhost: 1433; DatabaseName = DB_ShowData", "sa", "123456"); if (conn! = Null) {System. out. println ("database connection succeeded") ;}} catch (Exception e) {// TODO Auto-generated catch blocke. printStackTrace () ;}// return the Connection object return conn;}/*** @ param args */public static void main (String [] args) {// TODO Auto-generated method stub SelectQuery getcon = new SelectQuery (); getcon. getConnection (); // query/** String SQL = "SELECT * FROM [dbo]. [User] "; try {PreparedStatement Ps = getcon. conn. prepareStatement (SQL); ResultSet Rs = Ps.exe cuteQuery (); while (Rs. next () {String name = Rs. getString ("Name"); String password = Rs. getString ("Password"); System. out. println (name); System. out. println (password);} Rs. close (); Ps. close ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} ** // Add/** try {Statement statement = getcon. conn. createStatement (); int count = statement.exe cuteUpdate ("insert into [dbo]. [User] VALUES ('superman ', '000000') "); getcon. conn. close (); System. out. println ("the number of added rows is" + count);} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} ** // Delete try {Statement statement = getcon. conn. createStatement (); int count = statement.exe cuteUpdate ("delete from [dbo]. [User] WHERE Name LIKE 'superman '"); getcon. conn. close (); System. out. println ("successfully deleted rows" + count);} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();}}}

The running result is as follows:

OK! I will briefly introduce the JDBC operations here, and we will continue to study them in depth later. As my learning notes, I hope this will be helpful for me to learn new Java soon, however, I am targeting SQL databases. Other databases are different in creating connections. The interfaces in JDBC are similar in usage. You can try writing them down!