The JDBC API allows users to access any form of tabular data, especially data stored in a relational database.
Execution process:
• Connect to a data source, such as a database.
• Pass queries and update directives for the database.
• Processing the database response and returning the results.
JDBC Architecture
divided into two-tier architecture and three-layer architecture.
Double
Role: In this architecture, Java applets or applications directly access the data source.
Condition: Requires Driver to interact with the database being accessed.
Mechanism: The user command is passed to the database or other data source, and the result is returned.
Deployment: The data source can be on another machine, the user is connected through the network, called C/s configuration (can be intranet or Internet).
Three floors
The side architecture is special in that it introduces middle-tier services.
Process: Both the command and the structure pass through that layer.
Attraction: You can increase access control for enterprise data, as well as multiple types of updates, and also simplify deployment of applications and, in most cases, performance benefits.
Historical trends: In the past, because of performance problems, the middle tier is written in C or C + +, along with the optimization compiler (the conversion of Java bytecode to efficient specific machine code) and the development of technology, such as Ejb,java started for the development of the middle tier This also makes Java's advantages appear, using Java Code language, JDBC is followed by attention.
JDBC Programming Steps
To load the driver:
Class.forName (Driverclass)
//load MySQL driver
class.forname ("Com.mysql.jdbc.Driver")
//load Oracle driver
Class.forName ("Oracle.jdbc.driver.OracleDriver")
To obtain a database connection:
Copy Code code as follows:
Drivermanager.getconnection ("Jdbc:mysql://127.0.0.1:3306/imooc", "root", "root");
To create a Statement\preparedstatement object:
Copy Code code as follows:
Conn.createstatement ();
Conn.preparestatement (SQL);
Full instance
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;
Import java.sql.Statement;
public class Dbutil {public
static final String URL = ' Jdbc:mysql://localhost:3306/imooc ';
public static final String USER = "Liulx";
public static final String PASSWORD = "123456";
public static void Main (string[] args) throws Exception {
//1. Load driver
class.forname ("Com.mysql.jdbc.Driver");
//2. Get a database connection
Connection conn = drivermanager.getconnection (URL, USER, PASSWORD);
3. Operation of the database, the realization of additions and deletions to check
Statement stmt = Conn.createstatement ();
ResultSet rs = stmt.executequery ("Select user_name, age from Imooc_goddess");
If there is data, Rs.next () returns True while
(Rs.next ()) {
System.out.println (rs.getstring ("user_name") + "Age:" + Rs.getint ("Age"));}}
Check and delete and change
public class Dbutil {public static final String URL = ' JDBC:MYSQL://LOCALHOST:3306/IMOOC ';
public static final String USER = "Liulx";
public static final String PASSWORD = "123456";
private static Connection conn = null;
static{try {//1. Load driver Class.forName ("Com.mysql.jdbc.Driver"); 2.
Get database Connection conn = Drivermanager.getconnection (URL, USER, PASSWORD);
catch (ClassNotFoundException e) {e.printstacktrace ();
catch (SQLException e) {e.printstacktrace ();
} public static Connection getconnection () {return conn;
}//model package Liulx.model;
Import Java.util.Date;
public class Goddess {private Integer id;
Private String user_name;
Private Integer sex;
Private Integer age; Private Date birthday;
Note that the java.util.Date private String email;
Private String Mobile;
Private String Create_user;
Private String Update_user;
Private Date create_date;
Private Date update_date;
Private Integer Isdel;
Getter Setter Method ... }//---------DAOLayer--------------Package Liulx.dao;
Import Liulx.db.DbUtil;
Import liulx.model.Goddess;
Import java.sql.Connection;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.sql.Statement;
Import java.util.ArrayList;
Import java.util.List; public class Goddessdao {//Add public void Addgoddess (Goddess g) throws SQLException {//get connection Connection conn = dbut
Il.getconnection (); SQL String sql = "INSERT into imooc_goddess (user_name, sex, age, birthday, email, mobile," + "Create_user, Create_da
Te, Update_user, Update_date, Isdel) "+" VALUES ("+"?,?,?,?,?,?,?, current_date (),?, current_date (),?) "; Precompiled PreparedStatement ptmt = conn.preparestatement (sql);
Precompiled SQL, reduce SQL execution//ptmt.setstring (1, G.getuser_name ());
Ptmt.setint (2, G.getsex ());
Ptmt.setint (3, G.getage ());
Ptmt.setdate (4, New Date (G.getbirthday (). GetTime ()));
Ptmt.setstring (5, G.getemail ());
Ptmt.setstring (6, G.getmobile ());
Ptmt.setstring (7, G.getcreate_user ()); Ptmt.setstring (8, G. Getupdate_user ());
Ptmt.setint (9, G.getisdel ());
Implementation of Ptmt.execute ();
public void Updategoddess () {//get connection Connection conn = Dbutil.getconnection ();
SQL, each line with space String sql = "UPDATE imooc_goddess" + "set user_name=?, sex=?, age=, birthday=, email=?, mobile=?," + "Update_user=, Update_date=current_date (), isdel=?"
+ "where id=?"; Precompiled PreparedStatement ptmt = conn.preparestatement (sql);
Precompiled SQL, reduce SQL execution//ptmt.setstring (1, G.getuser_name ());
Ptmt.setint (2, G.getsex ());
Ptmt.setint (3, G.getage ());
Ptmt.setdate (4, New Date (G.getbirthday (). GetTime ()));
Ptmt.setstring (5, G.getemail ());
Ptmt.setstring (6, G.getmobile ());
Ptmt.setstring (7, G.getupdate_user ());
Ptmt.setint (8, G.getisdel ());
Ptmt.setint (9, G.getid ());
Implementation of Ptmt.execute ();
public void Delgoddess () {//get connection Connection conn = Dbutil.getconnection ();
SQL, each line with space String sql = "Delete from imooc_goddess where id=?"; precompiling sql, reducing SQL execution Preparedstatement ptmt = conn.preparestatement (sql);
Parameter Ptmt.setint (1, id);
Implementation of Ptmt.execute ();
Public list<goddess> query () throws SQLException {Connection conn = dbutil.getconnection ();
Statement stmt = Conn.createstatement ();
ResultSet rs = stmt.executequery ("Select user_name, age from imooc_goddess");
list<goddess> GS = new arraylist<goddess> ();
Goddess G = null;
while (Rs.next ()) {g = new Goddess ();
G.setuser_name (rs.getstring ("user_name"));
G.setage (Rs.getint ("Age"));
Gs.add (g);
} return GS;
Goddess Get () {goddess G = null;
Get connection Connection conn = Dbutil.getconnection ();
SQL, each line with space String sql = "SELECT * from imooc_goddess where id=?";
precompiling sql, reducing SQL execution PreparedStatement ptmt = conn.preparestatement (sql);
Parameter Ptmt.setint (1, id);
Execute ResultSet rs = Ptmt.executequery ();
while (Rs.next ()) {g = new Goddess ();
G.setid (Rs.getint ("id"));
G.setuser_name (rs.getstring ("user_name")); G.setAge (Rs.getint ("Age"));
G.setsex (Rs.getint ("Sex"));
G.setbirthday (rs.getdate ("Birthday"));
G.setemail (rs.getstring ("email"));
G.setmobile (Rs.getstring ("mobile"));
G.setcreate_date (Rs.getdate ("create_date"));
G.setcreate_user (rs.getstring ("Create_user"));
G.setupdate_date (Rs.getdate ("update_date"));
G.setupdate_user (rs.getstring ("Update_user"));
G.setisdel (Rs.getint ("Isdel"));
} return G; }
}