JSP (3)------JDBC Programming 1

JDBC API:

is a series of programming interfaces that can be used to connect databases, access data, and so on.

DriverManager: Driver management class, which is used to mount the driver and provides support for creating a database connection.

Connection: Used to connect to a specified database

Statement: Provides a way to execute an SQL statement to get the results of a query. There are two sub-interfaces. respectively:

• Preparestatement: SQL statement used to perform precompilation

ResultSet: Provides a way to process the result set

JDBC Driver API

is the interface for driver developers, there are four main types of JDBC drivers:

• JDBC-ODBC Bridge: Delegate all JDBC calls to other programming interface calls

• Local API drivers for some Java technologies: partially Java-based, other code implementations of delegated to local clients

• All Java technology-based local API drivers

• All Java technology-based local protocol drivers.

To write a JDBC application:

First, create the table in MySQL:

/* Create user Table */create table tbl_user (id int (one) unsigned NOT null auto_increment,name varchar (IN) NOT null default ' ', Password V Archar (+) NOT null default ", email varchar (+) Default", primary key (ID)) engine = InnoDB/*mysql storage engine, support transaction */defaul T charset = utf8;/* create Address Table */create table tbl_address (id int (one) unsigned NOT NULL auto_increment,city varchar) Default Nu Ll,country varchar () default null,user_id int (one) unsigned NOT NULL,/* PRIMARY key */primary key (ID) for user table) engine = Innodbdefault C Harset = UTF8;

The following is displayed in the database:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6E/2C/wKiom1V1X2rQyupBAAA7DZ4IoFo049.jpg "title=" Qq20150608172330.jpg "alt=" Wkiom1v1x2rqyupbaaa7dz4iofo049.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/2D/wKiom1V1X-XwqV3eAADX1QSglL0293.jpg "style=" float: none; "title=" qq20150608172543.jpg "alt=" Wkiom1v1x-xwqv3eaadx1qsgll0293.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/28/wKioL1V1YYnRN9LlAADN0HsV_xw343.jpg "style=" float: none; "title=" qq20150608172644.jpg "alt=" Wkiol1v1yynrn9llaadn0hsv_xw343.jpg "/>

/* Insert data into the table */insert into Tbl_user (ID, name, password, email) VALUES (1, ' xiaoming ', ' 123456 ', ' [email protected] '), (2, ' Daming ', ' 654321 ', ' [email protected] ', insert into tbl_address (city, Country, user_id) VALUES (' Beijing ', ' China ', 1), (' NewYork ', ' USA ', 2);

Steps for JDBC Programming:

• Load Driver

• Open connection

• Execute Query

• Processing results

• Clean up the environment

Add Java Code Test: Export the data from the user table

package com.jike.jdbc;import java.sql.connection;import java.sql.drivermanager;import  Java.sql.resultset;import java.sql.statement;public class jdbctest {public static  void main (String[] args)  {String sql =  "Select * from tbl_ User "; connection conn = null;   //  the current data connection statement st = null;    //  Send SQL statements to the database resultset rs = null;   //  Encapsulates data that is queried from a database//  automatically imports the above three interface Try {class.forname ("Com.mysql.jdbc.Driver") with Ctrl+shift+o; //  Register the JDBC driver for MySQL   conn = drivermanager.getconnection ("Jdbc:mysql://localhost:3306/info",   "******",  "*******");//  get the database connection, followed by two database user name and password st = conn.createstatement ();rs  = st.executequery (SQL);   //  send SQL statement while (Rs.next ()) {System.out.print ("id") + " "); System.out.print (Rs.GetString ("name") + " "); System.out.print (rs.getstring ("password") + " "); System.out.print (rs.getstring ("email") + " "); System.out.println ();}}  catch  (exception e)  {// todo: handle exceptione.printstacktrace ();} finally{ //  close order from small to large try{rs.close ();} catch (Exception e2) {}try {st.close ();}  catch  (EXCEPTION E3)  {// todo: handle exception}try {conn.close ();}  catch  (EXCEPTION E4)  {// todo: handle exception}}}

JDBC Database INSERT, UPDATE, delete

package com.jike.jdbc;import java.sql.connection;import java.sql.drivermanager;import  java.sql.resultset;import java.sql.statement;public class jdbctest {//  getting the database connection public  static connection getconnection () {connection conn = null;try { Class.forName ("Com.mysql.jdbc.Driver");   conn = drivermanager.getconnection ("jdbc:mysql:// Localhost:3306/info ", " root ", " mysqladmin ");}  catch  (exception e)  {// todo: handle exceptione.printstacktrace ();} Return conn;}   Inserting data into a database table Public static void insert () {connection conn = getconnection (); try {string sql =  "Insert into tbl_user (name, password, email)" + " VALUES (' Deci ',  ' 2255225 ',  ' [email protected] '); Statement st = conn.createstatement (); int count = st.executeupdate (SQL); System.out.println ("toThe " + count + " record is inserted in the database); Conn.close ();}  catch  (exception e)  {// todo: handle exceptione.printstacktrace ();}}   Update Content Public static void update () {connection conn = getconnection (); try  {String sql =  "Update tbl_user set email= ' [email protected] '   where name =  ' Deci ' "; Statement st = conn.createstatement (); int count = st.executeupdate (SQL); SYSTEM.OUT.PRINTLN ("Update to Database"  + count +  "records"); Conn.close ();}  catch  (exception e)  {// todo: handle exceptione.printstacktrace ();}} Delete Record Public static void delete () {connection conn = getconnection (); try { string sql =  "delete from tbl_user where name =  ' Deci '"; Statement st = conn.createstatement (); Int count = st.executeUpdate (SQL); System.out.println ("delete"  + count +  "records" from the database); Conn.close ();}  catch  (exception e)  {// todo: handle exceptione.printstacktrace ();}} Public static void main (String[] args)  {//insert ();//update ();d elete ();}}

Call the defined method in turn to get the result:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6E/71/wKiom1V8Ya3Ty8hqAAIrJraJzDA435.jpg "title=" Qq20150614005947.jpg "alt=" Wkiom1v8ya3ty8hqaairjrajzda435.jpg "/>

JDBC Programming----Transaction Processing:

Transactions: Work units that maintain data consistency, either all executed or not.

Basic characteristics of a transaction:

• Atomic Nature

• Consistency

• Isolation of

• Durability

Transaction-related SQL statements

Start transaction: BEGIN TRANSACTION

COMMIT TRANSACTION: Commit Transaction

ROLLBACK TRANSACTION: ROLLBACK TRANSACTION

Example: Inserting data into the user table and the Address table, respectively:

User Table Insert:

"INSERT into Tbl_user (ID, name, password, email)" + "values (' Tom ', ' 2525252 ', ' [email protected] ')"

Address Table Insert:

"INSERT into tbl_address (ID, city, country, user_id)" + "values (1, ' Shanghai ', ' China ', ' 10 ')";

package com.jike.jdbc;import java.sql.connection;import java.sql.drivermanager;import  java.sql.statement;public class transactiontest {public static connection  Getconnection () {connection conn = null;try {class.forname ("Com.mysql.jdbc.Driver"); conn  = drivermanager.getconnection ("Jdbc:mysql://localhost:3306/info",  "root", "mysqladmin");}  catch  (exception e)  {// todo: handle exceptione.printstacktrace ();} Return conn;}   Inserting data into the user table Public static void insertuserdata () {connection conn =  Getconnection ();try {string sql =  "Insert into tbl_user (id, name,  Password, email) "+" VALUES (10,  ' Tom ',  ' 2525252 ',  ' [email protected] ') "; Statement st = conn.createstatement (); int count = st.executeupdate (SQL); System.out.println ("+count+" bar data is inserted into the user table);}  catch  (exception e)  {// todo: handle exceptione.printstacktrace ();}}   Inserting data into the Address Table public static void insertaddressdata () {connection conn =  Getconnection ();try {string sql =  "Insert into tbl_address (id, city,  country, user_id) "+" VALUES (1,  ' Shanghai ',  ' China ',  ' 10 ');//Note here,  Because this statement specifies that the data ID number in the Insert Address Table is 1, the Address table already has id=1 data, and therefore throws an exception//  this is a very serious problem because the data is only partially inserted. Transactions are required to ensure that data is either fully inserted or rolled back. Statement st = conn.createstatement (); int count = st.executeupdate (SQL); System.out.println ("+count+" entry in the Address table);}  catch  (exception e)  {// todo: handle exceptione.printstacktrace ();}} Public static void main (String[] args)  {insertuserdata (); InsertAddressData ();}}

Program reported an exception

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6E/6D/wKioL1V8bWPz3blyAAPCUg8UNTg851.jpg "title=" Qq20150614014247.jpg "alt=" Wkiol1v8bwpz3blyaapcug8untg851.jpg "/>

This is the result of the insert in the data table as follows:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6E/6D/wKioL1V8baPR-rVSAAEyfIe30qs590.jpg "title=" Qq20150614014404.jpg "alt=" Wkiol1v8bapr-rvsaaeyfie30qs590.jpg "/>

It is visible that the information in the Address table is not inserted correctly, and the information in the user table is inserted successfully, which is very dangerous and cannot maintain the integrity of the data. Therefore, transactions are used to handle this type of problem.

Geek Academy Address: http://www.jikexueyuan.com/course/625.html

JSP (3)------JDBC Programming 1