Shulou(Shulou.com)06/01 Report--

JDBC is a database connection, which is a Java API that executes SQL statements. The program can connect to the relational database through JDBC API and use the structured query language to query and update the database. By using JDBC, you can use the same API to access different database systems, developers write applications for JDBC API, and then install drivers for different databases according to different databases. The database driver is the conversion layer between the JDBC program and the database. The database driver is responsible for mapping JDBC calls to specific database calls. When you need to connect to a particular database, you must have a corresponding database driver. A common type of JDBC driver: interact directly with a database instance. This driver is intelligent and knows the underlying protocol used by the database. This driver avoids native code, reduces the complexity of application development, and reduces the possibility of conflicts and errors.

Programs can use JDBC API to connect to different databases in a unified way, then execute standard SQL statements through Statement objects, and get the results of SQL statements accessing the database. Through the use of JDBC,java programs can be very convenient to operate a variety of mainstream databases, because of the cross-platform nature of java language, the programs written by JDBC can not only achieve cross-database, but also collapse the platform, with good portability.

SQL is a structured query language and a standard language for operating and retrieving relational databases. DML refers to the database operation language, which is mainly completed by three keywords: insert, update and delete. DDL refers to the data definition language, which operates the statements of database objects. It is mainly completed by four keywords: create, alter, drop and Truncate. The most basic database object is the data table, which represents the logical unit of storing data. DCL refers to the data control language, which is mainly completed by the two keywords grant and revoke. The DCL statement authorizes the database user, or revokes the specified user rights.

The general constraint type for Mysql databases: not null;UNIQUE constraints (when a unique constraint is created, Mysql establishes a unique index on the column or combination of columns in which the unique constraint resides). Primary key: a primary key constraint is equivalent to a combination of a non-null constraint and a unique constraint, allowing at most one primary key in each table, but this primary key constraint can be composed of multiple data columns.

FOREIGN KEY constraint: mainly used to ensure the referential integrity between one or two data tables. Foreign keys are the reference relationship between two fields of a table or two fields of two tables. When the master table record is referenced by the slave table record, the master table record is not allowed to be deleted. You must delete all records that reference the record from the table before you can delete the record of the master table. When a foreign key constraint is established, Mysql also indexes the column. External constraints are often used to define one-to-one and one-to-many relationships between two entities. If you want foreign key constraints in MySql to take effect, you should use table-level constraint syntax. If you need to specify the name of the foreign key constraint explicitly, you can use constraint to specify the name.

If you want to define that when deleting master table records, slave table records will also be deleted, you need to add on delete cascade or on delete set null after establishing foreign key constraints. The first is that when deleting master table records, all slave table records that refer to master table records are cascaded to delete. The second is to specify that when the master table record is deleted, the slave table record referencing the master table record is set to null

It is worth pointing out that foreign key constraints can refer not only to other tables, but also to themselves, which is often referred to as autocorrelation.

Index: an index is a database object stored in a schema. Although an index always belongs to a data table, it belongs to a database object as well as a data table. The only function of creating an index is to speed up the query of the table. The index uses the fast path access method to quickly locate the data, thus reducing the disk IBO.

How to create an index:

(1) automatic: automatically create indexes when primary key constraints, unique constraints, and external check constraints are defined on the table

(2 Create index... Statement to create the index. Create index index_name on

Table_name (column)

(3) Drop index to delete the index

Database maintenance of indexes requires a certain amount of system overhead, so special attention should be paid when creating indexes, generally creating indexes on table fields that need to be queried frequently.

Note: the delete from statement can delete multiple rows at a time and is qualified by the where clause.

When querying Select, it can be followed by not only data columns, but also expressions, variables, constants, and so on. For Mysql, if null appears in an arithmetic expression, it will cause the return value of the entire arithmetic expression to be null;, so you can use the following method to set the record for null to 0 to perform arithmetic operations:

Select avg (ifnull (java_teacher,0)) fromstudent_table

Note:

If you need to filter groups, you should use the having clause. There are some differences between the Having clause and where:

Groups cannot be filtered in the where clause, and the where clause is only used to filter rows. Filter groups must use the having clause

You cannot use a function in the where clause before the having clause can use a function.

The statement is as follows:

Select* from student_table group by java_teacher having count (*) > 2

Outer join: the so-called outer join is to add the outer connector wrapped in parentheses after the column name of the connection condition, which is called the left outer connection when the outer connector appears on the left and the right outer connection when it appears on the right. An outer join is to add a "universal row" to the table where the outer connector is located, where all the data recorded in this row is null, and the row can be matched with records in another table that do not meet the criteria, so that all records in another table can be selected, regardless of whether the records meet the join conditions or not.

The so-called self-join is to use a table as two tables, which requires two aliases for a table, and all data columns used in the query are prefixed with table aliases, because the data columns of the two tables are exactly the same.

Left, right, full outer connection: these three kinds of connection conditions use left join,right join and full join respectively. The connection conditions of these three outer connections are also specified by the on clause, which can be either equivalent or non-equivalent connection conditions. The Sql99 left outer join will list all the records in the left table that do not meet the join conditions, and the right outer join will list all the records on the right that do not meet the join conditions. A full external join lists all records in both tables that do not meet the join criteria.

Typical usage of JDBC

DriverManager: a driver service class for managing JDBC, which is used to obtain Connection objects. Connection: represents a database connection object, and each Connection represents a physical connection session. Statement: a tool interface for executing SQL statements. This object can execute DDL and DML statements. PreparedStatement: a precompiled Statement object. This method returns the precompiled Statement object, which submits the SQL statement to the database for precompilation. It allows the database to precompile SQL statements and then change the parameters determined by SQL each time to avoid the database having to compile SQL statements every time, so the performance is better. As opposed to statement, when it executes SQL statements, it no longer needs to pass in sql statements, just pass in parameters for precompiled SQL statements. ResultSet: result set object.

General steps for JDBC programming:

(1) load the database driver

Class.forName (driverClass)

Class.forName ("com.mysql.jdbc.Driver"); / / load the driver code of mysql:

Class.forName ("oracle.jdbc.driver.oracleDriver"); / / load the driver code for oracle

(2) obtain the database connection through DriverManager.

The URL of DriverManager.getConnection (String url,String user,String pass) / / MySql is written as follows: jdbc:mysql://hostname:port/databasename

Compared with Statement, using PreparedStatement not only improves execution efficiency, but also has another advantage: when parameters are used in SQL statements, there is no need to concatenate sql strings, while PreparedStatement only needs to use question mark placeholders to replace these parameters, which reduces the complexity of programming and has another advantage-to prevent SQL injection.

For example, the following validate () method uses PreparedStatement to perform validation:

Private Boolean validate (String username,String userpass) {

Try {

Connection conn=DriverManager.getConnection (url,user,pass)

PreparedStatement pstmt=conn.prepareStatement (

"select * from jdbc_test where jdbc_name=?and jdbc_desc=?"))

{

Psmt.setString (1m username)

Psmt.setString (2Jing password)

Try (

ResultSet rs=pstmt.executeUpdate ()

{

If (rs.next ()) {

Return true

}

}

Catch (Exceptione) {

E.printStackTrace ()

}

Returnfalse

}

Blob: it means a long binary object and is usually used to store large files. Typical Blob content is a picture or sound file.

You can use CachedRowSet for paging.

Transaction handling in Mysql:

A thing is a logical execution unit composed of a sequence of one-step or several-step database operations, which are either performed or abandoned. Generally speaking, a program may contain multiple transactions. For any database, things are very important, transactions are the main means to ensure the integrity of the underlying data, there is no thing to support the database application, it will be very fragile.

Things have four characteristics:

Atomicity: things are the smallest unit of execution in a program and can no longer be divided.

Consistency: the result of the execution of things that must change the database from one consistent state to another.

Isolation: the execution of each thing does not interfere with each other, and the internal operation of any thing is isolated from other concurrent transactions.

Persistence: any changes made to the data are recorded in permanent memory once the transaction is committed. It is usually saved in a physical database.

These features are referred to as ACID for short.

Mysql turns off transactions by default (that is, autocommit is turned on). In order to turn on Mysql transactions, you can display the call as follows: set AUTOCOMMIT= {0 | 1}, 0 means to turn off autocommit. Autocommit is the opposite of opening a transaction. If autocommit is turned on, it closes the transaction, and turning off autocommit means opening the transaction.

Commit, whether it shows or implicitly commits, ends the current transaction; rollback, whether it shows rollback or implicit rollback, ends the current transaction.

You can call the setAutoCommit () method provided by Connection to turn off autocommit and open the transaction. Such as: conn.setAutoCommit (false)

If all the sql statements are executed successfully, the program can call the conn.commit () syntax to commit the transaction. If any of the sql statements fail, the transaction is rolled back through the conn.rollback () method.

Most of the time, you only need to insert (C), query (R), modify (U), delete (D) and other CRUD operations on the specified data table. A pattern string in sql that uses a percent sign (%) to represent any number of characters and an underscore (_) to represent one character.

Use connection pooling to manage connections:

The establishment and closure of database connections is a very resource-consuming operation, and frequent opening and closing connections will lead to poor system performance. The solution of database connection pool is that when the application starts, the system automatically establishes enough database connections and forms a connection pool. Every time the application requests a database connection, it does not need to reopen the connection. Instead, it removes the existing connection from the connection pool and no longer closes the database connection. Instead, it returns the connection directly to the connection pool. This can greatly improve the running efficiency of the program.

C3P0 performs better among multiple connection pooling programs, automatically cleaning not only Connection that is no longer in use, but also Statement and ResultSet objects.

(1) Operation examples of C3P0 connection pooling are as follows:

Publicclass C3P0Test {

Public static void main (String [] args) {

Connection conn=null

PreparedStatement pstm=null

ResultSet rs=null

ComboPooledDataSource cpds=new ComboPooledDataSource ()

Try {

Conn=cpds.getConnection ()

Pstm=conn.prepareStatement ("select * from account wherename=?")

Pstm.setString (1, "a")

Rs=pstm.executeQuery ()

Rs.next ()

System.out.println (rs.getDouble ("money"))

} catch (Exception e) {

E.printStackTrace ()

} finally {

JDBCUtils.close (conn, pstm,rs)

}

}

}

(2) JDBC also provides a batch update function, when using batch update, multiple sql statements will be collected as a batch operation and submitted at the same time. The following is an example:

Public class StatementBatchTest {

Publicstaticvoid main (String [] args) {

Connection conn=null

Statement stat=null

ResultSet rs=null

Try {

Conn=JDBCUtils.getConnection ()

Stat=conn.createStatement ()

Stat.addBatch ("create databasedb_batch")

Stat.addBatch ("use db_batch")

Stat.addBatch ("create table tb_batch" + "(id int primary key auto_increment, name varchar (20))")

Stat.addBatch ("insert into tb_batchvalues (null,'a')")

Stat.addBatch ("insert into tb_batchvalues (null,'b')")

Stat.addBatch ("insert into tb_batchvalues (null,'c')")

Stat.addBatch ("insert into tb_batchvalues (null,'d')")

Stat.executeBatch ()

System.out.println ("execution succeeded")

} catch (Exception e) {

E.printStackTrace ()

} finally {

JDBCUtils.close (conn, stat,rs)

}

}

}

(3) examples of batch updates in preparedStatement are as follows:

Publicclass PreparedStatementBatch {

Public static void main (String [] args) {

Connection conn = null

PreparedStatement ps = null

ResultSet rs = null

Try {

/ / conn = JDBCUtils.getConnection ()

Class.forName ("com.mysql.jdbc.Driver")

Conn = DriverManager.getConnection ("jdbc:mysql:///db_batch", "root", "root")

Ps = conn.prepareStatement ("insert into tb_batch values (null,?")

For (int item0; I

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.