Access the database Mysql using Java

Access the database Mysql using Java
I. Overview

This article mainly introduces the basic methods and steps of Java successive databases, and briefly describes several key points.

2. database access steps

To access a database in Java, perform the following steps:

1. Load database driver
2. Register a database driver
3. Establish a connection to the database
4. Access Database

   First, you must call the Class. forName () loads and registers the mysql Driver Class. After loading the driver class, you need to register an instance of the driver class. The DriverManager class is responsible for managing the driver, this class provides the registerDriver () method to register an instance of the driver class, and we do not need to call this method in person, because the driver class of the Drive Interface contains static code blocks, in this Code block, the registerDriver () method is called to register an instance.

   Call the getConnection method of the DriverManager class to establish a connection to the database. After a connection is established, you need to access the database. The java. SQL package defines three interfaces: Statement, PrepareStatement, and CallableStatement, which correspond to different call methods respectively. Where:

   Statement: used to execute static SQL statements.

   PrepareStatement: inherited from the Statement interface. Its object represents a pre-compiled SQL Statement, which is obtained by calling the prepareStatement () method of the Connection object.

   CallableStatement: used to execute the SQL stored procedure. This interface is inherited from the PrepareStatement interface and the CallableStatement object is obtained by calling the prepareCall () method of the Connection object.

   The complete database access code is as follows:

   Package com. test; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. SQLException; import java. SQL. statement; public class CreateDB {public static void main (String [] args) {String url = "jdbc: mysql: // localhost: 3306"; String user = "root "; string password = "281889"; String driverclass = "com. mysql. jdbc. driver "; // JDBC class name try {// load the JDBC Driver. When this class is loaded, the Class Loader executes the static code block of the Class to register an instance Class of the driver. forName (driverclass); // establish the Connection conn of the database = DriverManager. getConnection (url, user, password); // access the database Statement stmt = conn. createStatement (); stmt.exe cute ("use information_schema"); int I = 0; ResultSet rs11_stmt.exe cuteQuery ("SELECT * FROM information_schema.SCHEMATA where SCHEMA_NAME = 'student '"); while (rs1.next () // determines whether student database I ++ is contained; if (I = 0) stmt.exe cuteUpdate ("create database student "); stmt.exe cuteUpdate ("use student"); int j = 0; ResultSet rs21_stmt.exe cuteQuery ("select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'student 'and TABLE_NAME = 'stuinfo '"); while (rs2.next () // determines whether the database contains stuinfo table j ++; if (j = 0) stmt.exe cuteUpdate ("create table stuinfo (sno INT not null primary key, name VARCHAR (50) not null, age int, sex VARCHAR (50)"); stmt. addBatch ("insert into stuinfo values (0420, 'abbin', 25, 'male')"); stmt.exe cuteBatch (); stmt. close (); stmt = null; conn. close (); conn = null;} catch (ClassNotFoundException e) {// catch Block e automatically generated by TODO. printStackTrace ();} catch (SQLException e) {// catch Block e automatically generated by TODO. printStackTrace ();}}

    

   Iii. Key Points 1. Differences between execute (String SQL), executeUpdate (String SQL), and executeQuery (String SQL:

   Execute: execute the SQL statement that returns multiple result sets.

   Returns:trueIf the first result is a ResultSet object

   　　　　　　 falseIf it is an update count or there are no results

   ExecuteUpdate: Execute SQL statements like insert, update, or delete.

   Returns :( 1) the row count for SQL Data Manipulation Language (DML) statements

   (2) 0 for SQL statements that return nothing

   ExecuteQuery: executes the specified SQL statement and returns a ResultSet object to view the execution result.

   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 for performing database operations in the form of a logical table. Its object maintains a cursor pointing to the current data row. In the initial state, the cursor is placed before the first row, you can use the next () method to move the cursor to the next row.

   3. Differences between Statement and PreparedStatement (extracted from the http://www.jb51.net/article/58343.htm ):

   1. different syntaxes

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

   PreparedStatement supports precompilation? Number.

   2. Different Efficiency

   Statement sends an SQL Statement each time, which does not support caching and has low execution efficiency.

   PreparedStatement supports pre-compilation and is cached in the database. You only need to send parameters to ensure fast execution.

   3. Different security

   Statement is easy to inject.

   Injection: You can write special SQL statements to intrude into the database.

   For example, to query the information of a user

   General situation: SELECT * FROM user_list where username = xxx and password = xxx; (here xxx Should Be your username and password)

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

   In this way, 1 = 1 Heng, and the "--" number is added before the password. The subsequent content becomes the comment and will not be executed. That is to say, you can query all user information without a password.

   PreparedStatement, which specifies parameters in SQL statements, can prevent injection.

   4. Determine whether a database exists in mysql:

   Stmt.exe cute ("use information_schema"); int I = 0; ResultSet rs11_stmt.exe cuteQuery ("SELECT * FROM information_schema.SCHEMATA where SCHEMA_NAME = 'student '"); while (rs1.next ()) // determine whether the student database I ++ is contained; if (I = 0) stmt.exe cuteUpdate ("create database student ");

   5. check whether a table exists in the database:

   Int j = 0; ResultSet rs21_stmt.exe cuteQuery ("select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'student 'and TABLE_NAME = 'stuinfo'"); while (rs2.next ()) // determine whether the database contains stuinfo table j ++; if (j = 0) stmt.exe cuteUpdate ("create table stuinfo (sno INT not null primary key, name VARCHAR (50) not null, age int, sex VARCHAR (50 ))");