Javaweb Backend < eight > JDBC base (Full)

I. Introduction of JDBC

1, JDBC is the sun company to simplify operational data to launch a set of specifications. The driver of the database vendor is the implementation of JDBC.

2, Java Data Base Connectivity (Java database connection), it is mainly composed of interfaces.

java.sql.* javax.sql.* JDK

Drive jar Package for database (Mysql-connector-java-5.0.8-bin.jar)

Second, the code of the JDBC step

0, premise: Copy the database drive to the build path (CLASSPATH)

1. Registered Driver

2. Get a link to the database

3. Create an object that represents the SQL statement

4. Execute SQL statements

5, if it is a query statement, you need to traverse the result set

6. Release the resources that are occupied

public class JdbcDemo1 {public static void main (string[] args) throws SQLException {//1, registered driver drivermanager.registerdrive R (New Com.mysql.jdbc.Driver ());//2, get the link to the database connection conn = Drivermanager.getconnection ("Jdbc:mysql://localhost : 3306/day15 "," Root "," "); No password "" "Empty//System.out.println (Conn.getclass (). GetName ()); To know the exact type, just do//3, create an object representing the SQL statement statement stmt = Conn.createstatement ();//4, Execute SQL statement ResultSet rs = stmt.executequery (" Select Id,name,password,email,birthday from users "),//5, if it is a query statement, you need to traverse the result set while (Rs.next ()) {System.out.println ("----- ----------------"); System.out.println (Rs.getobject ("id")); System.out.println (Rs.getobject ("name")); System.out.println (Rs.getobject ("password")); System.out.println (Rs.getobject ("email")); System.out.println (Rs.getobject ("Birthday"));}　　6. Release occupied resources Rs.close (); Stmt.close (); Conn.close (); }}

Third, the common interface in JDBC is detailed

1, DriverManager:

Role:

A, registered driver:

Mode one: (Not recommended)

Drivermanager.registerdriver (New Com.mysql.jdbc.Driver ());

Reason: 1, rely on specific drivers. 2, cause the driver registration 2 times

Way two: (suggestion)

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

B. Get a link to the database

Protocol between Url:sun and database vendors. Refer to the documentation for the database specifically.

public static Connection getconnection (String url,string user,string password)

Throws SQLException

public static Connection getconnection (String url,properties info)

Throws SQLException

public static Connection getconnection (String URL)

Throws SQLException

2, Connection

All database operations are based on links.

Statement createstatement (): Creates a Statement object that sends SQL to the database.

2. Get a link to the database//mode one://connection conn = drivermanager.getconnection ("Jdbc:mysql://localhost:3306/day15", "Root", " Sorry ");//Mode two://properties props = new Properties ();//props.setproperty (" User "," root ");//  parameter name: Reference database documentation// Props.setproperty ("Password", "sorry");//props.setproperty ("Useunicode", "true");//encoding-related parameters//props.setproperty (" Characterencoding "," UTF8 ");//connection conn = Drivermanager.getconnection (" Jdbc:mysql://localhost:3306/day15 ", props);//mode three Connection conn = Drivermanager.getconnection ("jdbc:mysql://localhost:3306/day15?user=root& Password=sorry ");

Connection conn = drivermanager.getconnection ("jdbc:mysql://localhost:3306/day15?user=root&password=" );

3, Statement

Statement stmt = Conn.createstatement ();

Function: Represents an SQL statement object. You can send any SQL statement to the database

ResultSet executeQuery (String sql): SQL is typically a query statement

int executeupdate (String sql): SQL is typically a DML statement. Insert Update Delete. Returns a value that operates on several records.

Boolean execute (String sql): SQL can be any statement. The return value does not represent success or not. If it is a query statement, there is a result set, which returns True. Returns false if no result set is returned.

4, ResultSet

Role: Encapsulates the result set of a query

ResultSet rs = stmt.executequery ("Select Id,name,password,email,birthday from users");　　list<user> users = new arraylist<user> (); while (Rs.next ()) {User user = new User ();　　User.setid (Rs.getint ("id"));　　User.setname (rs.getstring ("name"));　　User.setpassword (rs.getstring ("password"));　　User.setemail (rs.getstring ("email"));　　User.setbirthday (rs.getdate ("Birthday")); Users.add (user);}

Boolean next (): Cursor moves down. Return value Yes No record

Boolean previous (): Cursor moves up.

Boolean absolute (int count): Navigates to the specified row. The first line is 1.

void Beforefirst (): Moves the cursor to the front of the first row.

void Afterlast (): Moves the cursor to the back of the last line.

Iv. freeing up resources for use

Release resource//JDBC encoded code template public class JdbcDemo5 {public static void main (string[] args) {Connection conn = null; Statement stmt = null; ResultSet rs = null;try {class.forname ("Com.mysql.jdbc.Driver"); conn = Drivermanager.getconnection ("jdbc:mysql:// Localhost:3306/day15 "," root "," sorry "); stmt = Conn.createstatement (); rs = Stmt.executequery (" Select Id,name,password , Email,birthday from users "); list<user> users = new arraylist<user> (), while (Rs.next ()) {User user = new User (); User.setid (Rs.getint ("id" ), User.setname (rs.getstring ("name")), User.setpassword (rs.getstring ("password")), User.setemail (Rs.getstring (" Email ") User.setbirthday (rs.getdate (" Birthday ")); Users.add (user);}} catch (Exception e) {e.printstacktrace ();}
Finally {if (rs! = null) {try {rs.close ();　　　　　　　} catch (SQLException e) {e.printstacktrace (); } rs = null;}　　if (stmt! = null) {try {stmt.close ();　　} catch (SQLException e) {e.printstacktrace (); } stmt = null;}　　IF (conn! = null) {try {conn.close ();　　} catch (SQLException e) {e.printstacktrace ();　　} conn = null;}} }}

V. JDBC for CRUD (delete and change) operations

Tool Category:

Getconnection () return conn; Access Connect

The configuration file is placed in the Dbcgf.properties

Release () cast resource

Tool class public class Jdbcutil {private static string driverclass;private static string url;private static string user;private Static String password;static{try {ClassLoader cl = JdbcUtil.class.getClassLoader (); InputStream in = Cl.getresourceasst Ream ("Dbcfg.properties"); Properties props = new properties ();p rops.load (in);d Riverclass = Props.getproperty ("Driverclass"); URL = Props.getproperty ("url"), user = Props.getproperty ("user");p Assword = props.getproperty ("password"); Class.forName (Driverclass);} catch (Exception e) {throw new Exceptionininitializererror (e);}} public static Connection getconnection () throws exception{Connection conn = drivermanager.getconnection (Url,user, PASSW　　ORD); Return conn;} public static void Release (ResultSet rs,statement stmt,connection conn) {if (rs!=null) {try {rs.close ();} catch (Sqlexcep　　tion e) {e.printstacktrace ();} rs = null;}　　　　　　if (stmt!=null) {try {stmt.close ();　　} catch (SQLException e) {e.printstacktrace (); } stmt = null;} if (conn!=null) {try {conn.close ();] catch (SQLException e) {e.printstacktrace ();} conn = null;}}}

Tests:

Package Com.yif.jdbc;import Java.sql.connection;import Java.sql.resultset;import java.sql.statement;import 　　Org.junit.test;import Com.yif.util.jdbcutil;public class JdbcDemo6 {@Test public void Testadd () {Connection conn = null;　　Statement stmt = null;　　ResultSet rs = null;　　　　try{conn = Jdbcutil.getconnection ();　　　　stmt = Conn.createstatement (); Stmt.executeupdate ("INSERT INTO Users" (Name,password,email,birthday) VALUES (' Fan Qingxia ', ' 123 ', ' fqx@itcast.cn ', '　　2000-10-01 ') ");　　}catch (Exception e) {throw new RuntimeException (e);　　}finally{Jdbcutil.release (RS, stmt, conn);　　}} @Testpublic void Testupdate () {Connection conn = null;　　Statement stmt = null;　　ResultSet rs = null;　　　　try{conn = Jdbcutil.getconnection ();　　　　stmt = Conn.createstatement ();　　Stmt.executeupdate ("Update users set password=111 where id=4");　　}catch (Exception e) {throw new RuntimeException (e);　　}finally{Jdbcutil.release (RS, stmt, conn);　　}} @Testpublic void Testdelete () {Connection conn = null; StatemeNT stmt = null;　　ResultSet rs = null;　　　　try{conn = Jdbcutil.getconnection ();　　　　stmt = Conn.createstatement ();　　Stmt.executeupdate ("Delete from users where id=1");　　}catch (Exception e) {throw new RuntimeException (e);　　}finally{Jdbcutil.release (RS, stmt, conn); }}}

Javaweb Backend < eight > JDBC base (Full)