Java Access database MySQL

I. Overview

This paper mainly introduces the basic methods and steps of Java database, and gives a brief description of several key points.

Second, the database access steps

There are several main steps to accessing a database in Java:

1. Load Database Driver
2. Registering Database Drivers
3. Establish a connection to the database
4. Accessing the database

First, to invoke Class.forName () to load and register the MySQL driver class, after loading the driver driver class, you need to register an instance of the driver class, the DriverManager class is responsible for managing the driver, and this class provides Registerdriver () method to register an instance of the driver class, and we do not need to call this method ourselves, because the driver class of the drive interface contains a static block of code, which in this block of code calls the Registerdriver () method to register an instance of itself.

Then call the Getconnection method of the DriverManager class to establish a connection to the database. After the connection is established, access to the database is required. Three interfaces are defined in the java.sql package: Statement, Preparestatement, and CallableStatement, respectively, corresponding to different invocation modes. which

Statement: Used to execute a static SQL statement.

Preparestatement: Inherited from the statement interface, its object represents a precompiled SQL statement, which is obtained by invoking the Preparestatement () method of the Connection object.

CallableStatement: Used to execute a SQL stored procedure that inherits from the Preparestatement interface and obtains CallableStatement objects by invoking the Preparecall () method of the Connection object.

The complete Access database code is as follows:

 Packagecom.test;Importjava.sql.Connection;ImportJava.sql.DriverManager;ImportJava.sql.ResultSet;Importjava.sql.SQLException;Importjava.sql.Statement; Public classcreatedb{ Public Static voidMain (string[] args) {String URL= "jdbc:mysql://localhost:3306"; String User= "Root"; String Password= "281889"; String Driverclass= "Com.mysql.jdbc.Driver";//JDBC Class name        Try        {            //loading the JDBC driver, when the class is loaded, the ClassLoader executes a static code block of that class to register an instance of the driverClass.forName (Driverclass); //establishing a connection to a databaseConnection conn=drivermanager.getconnection (Url,user,password); //Accessing the databaseStatement stmt=conn.createstatement (); Stmt.execute ("Use Information_schema"); intI=0; ResultSet rs1=stmt.executequery ("SELECT * from INFORMATION_SCHEMA. schemata where schema_name= ' student ' ");  while(Rs1.next ())//determine if a student database is includedi++; if(i==0) Stmt.executeupdate ("CREATE DATABASE Student"); Stmt.executeupdate ("Use student"); intJ=0; ResultSet rs2=stmt.executequery ("SELECT * from INFORMATION_SCHEMA. TABLES where table_schema= ' student ' and table_name= ' Stuinfo ' ");  while(Rs2.next ())//determine if the database contains Stuinfo tablesJ + +; if(j==0) Stmt.executeupdate ("CREATE Table Stuinfo (Sno INT NOT NULL primary key,name varchar () not null,age int,sex varchar (50))"); Stmt.addbatch ("INSERT into stuinfo values (0420, ' Alpinresort ', 25, ' Male ')");            Stmt.executebatch ();            Stmt.close (); stmt=NULL;            Conn.close (); Conn=NULL; }         Catch(ClassNotFoundException e) {//TODO Auto-generated catch blockE.printstacktrace (); }         Catch(SQLException e) {//TODO Auto-generated catch blockE.printstacktrace (); }    }}

Iii. key points Description 1, execute (String sql), executeupdate (String sql), and executequery (String sql) differ:

Execute: Executes an SQL statement that returns multiple result sets.

　　Returns: true If the first result is a ResultSet object

　　　　　　 falseIf it is a update count or there is no results

executeupdate: Executes an SQL statement similar to insert, UPDATE, or delete.

　　Returns: (1) The row count for SQL Data manipulation Language (DML) statements

(2) 0 for SQL Statements this return nothing

ExecuteQuery : Executes the specified SQL statement, returning a ResultSet object that is used to view the results of the execution.

Returns:A ResultSet object that contains the data produced by the given query;


　　PS:the resultset returned by ExecuteQuery will never be null

　　2. ResultSet Object

　　The ResultSet object encapsulates the result set of a database operation as a logical table whose objects maintain a cursor to the current data row, and the initial state is marked before the first row, and the cursor can be moved to the next line by the next () method.

3. The difference between Statement and PreparedStatement (excerpt from http://www.jb51.net/article/58343.htm):

1. Different syntax

Statement only supports static compilation, and SQL statements are written dead.

PreparedStatement support pre-compilation, with? Number to occupy a position.

2. Different efficiency

statement sends an SQL statement each time, does not support the cache, the execution is inefficient.

PreparedStatement support precompilation, slow existence of the database, just send parameters, efficient execution.

3. Different security

Statement is easy to inject.

Injection: Cunning molecules can write special SQL statements to invade a database.

For example, to query a user's information

General: SELECT * from User_list where username=xxx and password=xxx; (here xxx should be for users to fill in their own user name and password)

Injection situation: SELECT * from user_list where username= ' abc ' or 1=1-password=xxx;

This 1=1 identity, and in front of the password with the "--" number, the following content becomes the comment is not executed. In other words, this allows you to query all user information without a password.

PreparedStatement, because the parameters in the SQL statement are specified, it prevents injection.

4. Determine if there is a database in MySQL:

Stmt.execute ("Use Information_schema");    int i=0;   ResultSet rs1=stmt.executequery ("select * from INFORMATION_SCHEMA. schemata where schema_name= ' student ' ");     while (Rs1.next ())  // determine if a student database      is included i++;    if (i==0)  stmt.executeupdate ("CREATE Database Student");

5. Determine if a table already exists in the database:

int j=0;   ResultSet rs2=stmt.executequery ("select * from INFORMATION_SCHEMA. TABLES where table_schema= ' student ' and table_name= ' Stuinfo ' ");     while // determine if the database contains Stuinfo tables      J + +;    if (j==0)      stmt.executeupdate ("CREATE TABLE Stuinfo (Sno INT NOT NULL primary key,name VARCHAR () is not NULL, Age Int,sex VARCHAR (50)) ");

　　

Java Access database MySQL