Basic knowledge of JDBC

The role of JDBC

JDBC provides a common API for Java access to databases, providing unified access to a variety of relational databases. Because SQL is the standard language of a relational database management system, as long as we follow the SQL specification, the code we write can access both MySQL and SQL Server.

To put it simply, JDBC can do three things:

• Establish a connection to the database
• Send SQL statement
• Processing results

Important classes in JDBC

Java.sql.DriverManager: Used to load different JDBC drivers and provide support for new database connections created;

Java.sql.Connection: Completes the connection function to a specified database; Java.sql.Statement: As a container for executing SQL statements in an already created connection (java.sql.Connection); It contains two important subclasses:

Java.sql.PreparedStatement: The SQL statement used to perform the precompilation;

Java.sql.CallableStatement: Used to execute stored procedures (Stored Procedure) that have been created in the database.

Java.sql.ResultSet: Represents the database result set after the execution of a specific SQL statement.

We can refer to the JDK API for these classes with very detailed instructions. And it's the ^_^ of Chinese.

Establishing a connection to a database (MySQL)

1.URL

The standard syntax for the JDBC URL is shown below. They are separated by colons:

< Protocol jdbc>:< Sub-Protocol >:< sub-name >

The protocol in the < protocol >:jdbc URL is always JDBC.

< sub-Protocol;: Name of the driver name or database connection mechanism. For example: MySQL

< data source;: Contains information such as the host, port, name, user name, password, and so on to connect to the database.

For example, the URL format for MySQL connection is:

jdbc:mysql//[hostname][:p ort]/[dbname][?param1=value1][&param2=value2] ....

One thing to note is that the MySQL port number (port) can be queried as follows :

Mysql>show Global variables like ' port ';

2. Using DriverManager to manage driver classes

Class.forName ("Com.mysql.jdbc.Driver");

3. Connection Management

Set not auto commit: Conn.setautocommit (false);

Submission data: Conn.commit ();

Rollback data: Conn.rollback ();

Set auto-commit: Conn.setautocommit (True).

Close connection: Conn.close ();

Here is an example of a test connection to SQL:

Send SQL "show Databases" and display the results.

    public static void Testconn () throws SQLException {        Connection conn = getconn ("root", "", "");        Statement stmt = Conn.createstatement ();        ResultSet rs = Stmt.executequery ("show Databases");        while (Rs.next ()) {            System.out.println (rs.getstring (""));        }    }

Establish a connection to the database

    public static Connection Getconn (string Username, string password,            string DBname) {        string driver = "COM.MYSQL.J Dbc. Driver ";        String url = "jdbc:mysql://localhost:3306/" + DBname;        Connection conn = null;        try {            class.forname (driver);            conn = drivermanager.getconnection (URL, username, password);        } catch (ClassNotFoundException e) {            e.printstacktrace ();        } catch (SQLException e) {            e.printstacktrace () ;        }        return conn;    }

databasemetadata-information about the database

Create a DatabaseMetaData object

DatabaseMetaData Datameta=conn.getmetadata ();

1. Getting the tables in the database

ResultSet gettables (String catalog, String Schemapattern, String Tablenamepattern, String types[]);

Catalog refers to a database

Schemapattern is the user of the database

Tablenamepattern is the table name

Types refers to table, view, etc.

The Gettables method returns a ResultSet object, and each record is a description of a table. Only those tables that meet the parameter requirements are returned. Each row of the result set has 8 fields, and the third one is the table name.

Get the table name in the database

public static string[] Gettablenames (String DBname, Connection conn) {        string[] tables = null;        try {            DatabaseMetaData DBmeta = Conn.getmetadata ();            String types[] = {"TABLE"};            ResultSet rs = dbmeta.gettables (DBname, NULL, NULL, types);            list<string> ls = new arraylist<string> ();            int i = 0;            while (Rs.next ()) {                Ls.add (rs.getstring (3));                i++;            }            tables = new String[i];            Ls.toarray (tables);        } catch (SQLException e) {            e.printstacktrace ();        }        return tables;    }

2. Get the information for each column in the table

ResultSet getcolumns (String catalog, String Schemapattern, String Tablenamepattern, String types[]);

GetColumns returns an object of the ResultSet class where each row is a description of a field, and only columns that meet the parameter requirements are returned.

The field information for each row is: database name, Database extension, table name, field name

Get all field names for a table

    public static string[] Getfieldsnames (String tableName, Connection conn) {        string[] fields = null;        try {            DatabaseMetaData DBmeta = Conn.getmetadata ();            ResultSet rs = dbmeta.getcolumns (null, NULL, tableName, NULL);            list<string> ls = new arraylist<string> ();            int i = 0;            while (Rs.next ()) {                Ls.add (rs.getstring (4));                i++;            }            Fields = new String[i];            Ls.toarray (fields);        } catch (SQLException e) {            e.printstacktrace ();        }        return fields;    }

3. Get information about the index

ResultSet getindexinfo (String catalog, String schema, Boolean unique, Boolean approximate);

The GetIndexInfo method returns an object of the ResultSet class, where each row is a description of an index, and only indexes that meet the parameter requirements are returned. Statement Submit SQL

Statement object sending SQL statement

Statement stmt = Conn.createstatement (); ResultSet rs = stmt.executequery ("Select a, B, C from Table1");

The statement interface provides three ways to execute SQL statements:

Method ExecuteQuery: A statement used to produce a single result set, such as a SELECT statement.

Method executeupdate: Used to execute INSERT, UPDATE, or DELETE statements, as well as SQL DL (data definition language) statements, such as CREATE table and drop table. The effect of an INSERT, update, or DELETE statement is to modify one or more columns in 0 or more rows in a table. The return value of Executeupdate is an integer that indicates the number of rows affected (that is, the update count). For statements that do not manipulate rows such as CREATE table or drop table, the return value of executeupdate is always zero.

Method execute: Used to perform statements that return multiple result sets, multiple update counts, or a combination of both.

The Statement object automatically closes the ResultSet object when the Statement object that generates the ResultSet object is closed, re-executed, or used to get the next result from a sequence of multiple results. This means that the processing of the current ResultSet object needs to be completed before the statement object is re-executed.

Stmt.close (); Explicitly close the statement object and release the DBMS resource. Result set ResultSet object

1.ResultSet Traversal

A ResultSet object corresponds to a table returned by a query statement that contains all the query results. In fact, we can consider a ResultSet object as a two-dimensional table. The processing of the ResultSet object must be done line by row, and the individual columns in each row can be processed in any order.

Line and cursor: The ResultSet object has a pointer to its current data row. Initially, the cursor is placed before the first line. The next method moves the cursor to the next line, because the method returns False when the ResultSet object has no next line, so it can be used in a while loop to iterate over the result set.

Get columns: Each column can be processed in any order when it is processed for each row. However, the processing of columns in left-to-right order can achieve high execution efficiency. The GetXXX () method of the ResultSet class can obtain the results of a search from a column. where xxx is the Java data type in JDBC, such as int, String, date, and so on.

ResultSet provides two ways to specify columns for retrieval:

One is to index an int value as a column, and the other is to index a string object as a column name. The first type is more efficient.

The following is an example of a variable result set:

    Statement statement=conn.createstatement ();    Create Statemetn object based on Connection object     String sql= "Select Int_colmn, STRING_COLMN,DATE_COLMN," + "byte_colmn from table _name ";    ResultSet result=statement.executequery (SQL);    Executes the SQL statement with the statement object, returning the result set while     (Result.next ()) {        int int_value=result.getint (1);        String string_value=result.getstring ("Colmn2");        Date  Date_value=result.getint (3);        Byte   byte_value[]=result.getstring ("Colmn4");        Obtain Data         out.println (int_value+ "" +string_value+ "" +date_value+ ") from the database in two different ways;        Output the results of the search on the user's browser    }

2. Getting information about a result set

ResultSetMetaData Rsdata=resultset.getmetadata ();

The GetMetaData () method returns an object of the ResultSetMetaData class, using the method of the class, to get a lot of information about the result set, here are a few common methods:

(1) getColumnCount (): Returns an int value indicating the number of columns in the result set;

(2) Getcolumnlabel (int column): Returns the display title of the column referred to by column, the value of the SQL as of field;

(3) getcolumnname (int column): Returns the original name of field. The string object returned by this method can be used as a parameter to the GetXXX () method of the ResultSet class. However, there is not much practical significance;

(4) getcolumntype (int comlumn): Returns the SQL data type of the specified column. Its return value is an int value. There are definitions of various SQL data types in the Java.sql.Types class;

(5) getcolumntypename (int comlumn): Returns the name of the data type of the specified column in the data source. Its return value is a string object; This is more commonly used.

(6) isnullable (int column): Returns a Boolean value that indicates whether the column allows a null value to be stored.

Get information about each field in a table

    Linkedhashmap Keep the insertion order public    static linkedhashmap<string, string> GetFields (String tableName,            Connection conn) {        linkedhashmap<string, string> fields = new linkedhashmap<> ();        String sql = "SELECT * from" + tableName;        Statement stmt;        try {            stmt = conn.createstatement ();            ResultSet rs = stmt.executequery (sql);            ResultSetMetaData Rsmeta = (resultsetmetadata) rs.getmetadata ();            int count = Rsmeta.getcolumncount ();            for (int i = 1; I <= count; i++) {                fields.put (Rsmeta.getcolumnlabel (i), rsmeta.getcolumntypename (i));            }        } catch (SQLException e) {            e.printstacktrace ();        }        return fields;    }

Basic knowledge of JDBC