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

1. Introduction of JDBC

   jdbc provides a unified programming interface for java developers to use the database, which consists of a set of java classes and interfaces.

The process of    accessing database

   in the connection process, the general beginners are MySQL and java on the same computer, establish a socket connection.

   common APIs:-generally use Driver APIs for java development.-when connecting to the database, you need to load data drivers from specific vendors:

   MySQL:Class.forname ('com.mysql.jdbc.Driver')

   Oracle:Class.forname ('oracle.jdbc.driver.OracleDriver')

Important API of 2.JDBC (take MySQL as an example)

Establish a connection:

@ Test public void getConnect () {try {/ / load driver Class.forName ("com.mysql.jdbc.Driver"); / / get connection String url= "jdbc:mysql://localhost:3306/library"; String user= "root"; String password= "123456"; Connection connection = DriverManager.getConnection (url, user, password) } catch (Exception e) {e.printStackTrace ();}}

Statement

Note: here Statement- is used to execute static SQL statements and return the result objects it generates, and here Statement has three classes:

 -Statement is created by createStatement and is used to send simple SQL statements. (without parameters)

 -PreparedStatement: inherited from the Statement parent class and created by preparedStatement, it is used to send SQL statements with one or more input parameters. PreparedStatement objects are more efficient than Statement objects and prevent SQL injection.

 -CallableStatement: inherited from PreparedStatement. Created by the method prePareCall for calling storage.

The common methods of Statement are:

 -execute (): runs a statement that returns whether there is a result set

 -executeQuery (): runs the select statement and returns the ResultSet result set

 -executeUpdate (): runs the insert/update/delete operation and returns the number of rows affected

Related codes:

@ Test public void testStatement () throws SQLException {/ / create Statement Statement statement = connection.createStatement (); String sql= "select * from book"; boolean isNull= statement.execute (sql); / / create PreparedStatement sql= "select * from book"; PreparedStatement preparedStatement1 = connection.prepareStatement (sql); ResultSet resultSet = preparedStatement1.executeQuery (); / / PreparedStatement prevents SQL injection, here? Indicates the placeholder sql= "select * from book where bid =?"; PreparedStatement preparedStatement2 = connection.prepareStatement (sql); preparedStatement2.setObject (1Magne2); preparedStatement2.executeQuery ();}

ResultSet

Description: ResultSet is mainly returned by the executeQuery () method

Flow chart for reading data:

Example:

@ Test public void testStatement () throws SQLException {/ / create PreparedStatement String sql = "select * from book"; PreparedStatement preparedStatement1 = connection.prepareStatement (sql); ResultSet resultSet = preparedStatement1.executeQuery (); while (resultSet.next ()) {/ / indicates whether there is another System.out.println ("bid" + resultSet.getInt (1)); System.out.println ("bname" + resultSet.getString (2)) System.out.println ("author" + resultSet.getString (3);}}

Batch processing of jdbc

Note: if you are using a large number of batches, it is recommended to use statement, because the precompilation space of preparedstatement is limited, and exceptions will occur when the amount of data is particularly large.

Example:

@ Testpublic void testStatement () throws SQLException {/ / create Statement Statement statement = connection.createStatement (); for (int I = 0; I < 50; iSum +) {/ / get the current system time long timestamp = System.currentTimeMillis (); String sql = "insert into t_date (timed timeline) values (" + timestamp + "," + i+ ")" / / add executed SQL statement statement.addBatch (sql) for batch processing;} / execute batch statement.executeBatch (); / / commit transaction connection.commit ();}

Transactions of jdbc

@ Testpublic void testTransaction () {/ / cancel auto-submission try {connection.setAutoCommit (false); / / insert a DML statement PreparedStatement stetm1 = connection.prepareStatement ("insert into book values (1001LJ 'picking up in the evening,' Lu Xun')"; stetm1.execute () / / insert another DML statement PreparedStatement stetm2 = connection.prepareStatement ("insert into book values (1001)); stetm2.execute (); / / submit connection.commit ();} catch (SQLException e) {try {/ / automatically rollback connection.rollback () after failure } catch (SQLException E1) {e1.printStackTrace ();} e.printStackTrace ();}}

CLOB operation:

It is used to store a large amount of text data, and the operation of large fields is often handled as a stream. Instead of ordinary fields, you can read them all at once.

/ / Code implementation: public class ReadAndWriteClob {public static void main (String [] args) {/ / get database connection try {Class.forName ("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection ("jdbc:mysql://localhost:3306/library", "root", "123456") / / clob field insert / / Table: user_info / / Field: name varchar, introduce clob String sql = "insert into user_info values (?)"; PreparedStatement prepared1 = conn.prepareStatement (sql); / / insert data prepared1.setObject (1, "user1") / / sets the insert text object. The second parameter is an input stream, which reads the file prepared1.setClob directly (2 new InputStreamReader new BufferedReader (new FileInputStream ("src/a.txt"); prepared1.execute () / / clob field reads sql= "select * from user_info"; PreparedStatement prepared2 = conn.prepareStatement (sql); ResultSet resultSet = prepared2.executeQuery (); while (resultSet.next ()) {Clob introduce = resultSet.getClob ("introduce"); Reader characterStream = introduce.getCharacterStream (); int temp=0 While ((temp=characterStream.read ())! =-1) {System.out.print ((char) temp);} catch (Exception e) {e.printStackTrace ();}

BLOB operation:

It is used to store a large amount of binary data, which can be stored in any type of file (audio, video, etc.).

/ / Code implementation public class ReadAndWriteClob {public static void main (String [] args) {/ / get database connection try {Class.forName ("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection ("jdbc:mysql://localhost:3306/library", "root", "123456") / / clob field insert / / Table: user_info / / Field: name varchar, headImg blob String sql = "insert into user_info values (?)"; PreparedStatement prepared1 = conn.prepareStatement (sql); / / insert data prepared1.setObject (1, "user1") / / sets the image object to be inserted. The second parameter is a basic input stream prepared1.setBlob (prepared1.execute ()); / / the clob field reads sql= "select * from user_info"; PreparedStatement prepared2 = conn.prepareStatement (sql); ResultSet resultSet = prepared2.executeQuery () While (resultSet.next ()) {Blob headImg = resultSet.getBlob ("headImg"); / / gets the basic stream, InputStream binaryStream = headImg.getBinaryStream (); int len=0; byte flush [] = new byte [1025]; OutputStream os=new FileOutputStream ("d:\\ c.jpg")) While ((len=binaryStream.read (flush))! =-1) {os.write (flush,0,len); os.flush ();} os.close (); binaryStream.close ();}} catch (Exception e) {e.printStackTrace () }}}

Encapsulation of jdbc

Here you need to load the configuration file:

Code implementation:

Public class jdbcUtils {private static Properties pro; static {pro = new Properties (); InputStream in = jdbcUtils.class.getClassLoader (). GetResourceAsStream ("dbproperties.properties"); pro.load (in); Class.forName (pro.getProperty ("driver"));} catch (Exception e) {e.printStackTrace () }} / / establish a connection to the database public static Connection getMySQLConn () {try {return DriverManager.getConnection (pro.getProperty ("url"), pro.getProperty ("user"), pro.getProperty ("password"));} catch (SQLException e) {e.printStackTrace ();} return null } / * shutdown order: resultSet--  preparStatement--  Connection * when closing, the close () of these three cannot be written in the same try {} catch {} * / public static void close (Statement stem,ResultSet rs,Connection conn) {if (rswatches null) {try {rs.close () } catch (SQLException e) {e.printStackTrace ();}} if (stemless null) {try {stem.close ();} catch (SQLException e) {e.printStackTrace () }} if (connexion null) {try {conn.close ();} catch (SQLException e) {e.printStackTrace ();}

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.