Basic application of JDBC

1. JDBC-----Full Name--------java Data Base Connectivity is a Java API for executing SQL statements that provides unified access to a variety of relational databases. It consists of a set of classes and interfaces written in the Java language.
2. The role of JDBC

establish a connection to the database, send manipulate the statements of the database and process the results.

What are the advantages of 3.PreparedStatement compared to statement?

1, relatively safe, can prevent SQL injection.

2, has the pre-compiling function, the same operation batch data efficiency is high.

3, using PreparedStatement interface, inherit from statement interface more flexible and more efficient than statement object.

4. JDBC API Main function: Establish connection with database, execute SQL statement, process result

DriverManager: Depending on the database, the JDBC driver is managed

Connection: Responsible for connecting to the database and serving the task of transmitting data

Statement: Generated by Connection, responsible for executing SQL statements

ResultSet: Responsible for saving the results of the query resulting from statement execution

(1) Define the class of the record (optional), this is mainly for the convenience of operation and interface definition, is not necessary.

static class Student {private String Id;        Private String Name;        Private String Sex;        Private String age; Student (String Name, String Sex, String age) {this. Id = null; Default this.            name = name; This.            sex = sex; This.        Age = age;        } public String GetId () {return Id; } public void SetId (String Id) {this.        id = ID;        } public String GetName () {return Name; public void SetName (String Name) {this.        name = name;        } public String Getsex () {return Sex; public void Setsex (String Sex) {this.        sex = sex;        } public String Getage () {return age; public void Setage (String age) {this.        Age = age; }}

(2) Access to the connection, before the operation must first obtain a connection to the database.

private static Connection Getconn () {    String driver = "Com.mysql.jdbc.Driver";    String url = "jdbc:mysql://localhost:3306/samp_db";    String username = "root";    String password = "";    Connection conn = null;    try {        class.forname (driver);//classloader, load corresponding driver        conn = (Connection) drivermanager.getconnection (URL, Username, password);    } catch (ClassNotFoundException e) {        e.printstacktrace ();    } catch (SQLException e) {        e.printstacktrace () ;    }    Return conn;}

(3). Inserting elements into a database

private static int Insert (Student Student) {    Connection conn = Getconn ();    int i = 0;    String sql = "INSERT into students (Name,sex,age) VALUES (?,?,?)";    PreparedStatement pstmt;    try {        pstmt = (preparedstatement) conn.preparestatement (SQL);        Pstmt.setstring (1, Student.getname ());        Pstmt.setstring (2, Student.getsex ());        Pstmt.setstring (3, Student.getage ());        i = Pstmt.executeupdate ();        Pstmt.close ();        Conn.close ();    } catch (SQLException e) {        e.printstacktrace ();    }    return i;}

(4). Modify the elements in the database

private static int update (Student Student) {    Connection conn = Getconn ();    int i = 0;    String sql = "Update students set age= '" + student.getage () + "' Where name= '" + student.getname () + "'";    PreparedStatement pstmt;    try {        pstmt = (preparedstatement) conn.preparestatement (SQL);        i = Pstmt.executeupdate ();        System.out.println ("Resutl:" + i);        Pstmt.close ();        Conn.close ();    } catch (SQLException e) {        e.printstacktrace ();    }    return i;}

(5). Querying data in the database

private static Integer GetAll () {    Connection conn = Getconn ();    String sql = "SELECT * from students";    PreparedStatement pstmt;    try {        pstmt = (preparedstatement) conn.preparestatement (SQL);        ResultSet rs = Pstmt.executequery ();        int col = Rs.getmetadata (). getColumnCount ();        System.out.println ("============================");        while (Rs.next ()) {for            (int i = 1; I <= col; i++) {                System.out.print (rs.getstring (i) + "\ t");                if ((i = = 2) && (rs.getstring (i). Length () < 8)) {                    System.out.print ("\ t");}             }            System.out.println ("");        }            System.out.println ("============================");    } catch (SQLException e) {        e.printstacktrace ();    }    return null;}

(6). Deletion of data in the database

private static int Delete (String name) {    Connection conn = Getconn ();    int i = 0;    String sql = "Delete from students where name= '" + Name + "'";    PreparedStatement pstmt;    try {        pstmt = (preparedstatement) conn.preparestatement (SQL);        i = Pstmt.executeupdate ();        System.out.println ("Resutl:" + i);        Pstmt.close ();        Conn.close ();    } catch (SQLException e) {        e.printstacktrace ();    }    return i;}

(7). Test code is correct

public static void Main (String args[]) {    jdbcoperation.getall ();    Jdbcoperation.insert (New Student ("Achilles", "Male", ")");    Jdbcoperation.getall ();    Jdbcoperation.update (New Student ("Bean", "" "," 7 "));    Jdbcoperation.delete ("Achilles");    Jdbcoperation.getall ();}

Driver Interface: Represents the Java driver interface, all specific database vendors are to implement this interface.

• 1.connect (url,properties): How to connect to a database
  
  • URL: The URL of the connection database (used to locate the specific database resource)
    
    • URL syntax: jdbc:mysql (Oracle, SQL Server, etc.)://IP address/port number of the host on which the database resides/database name to be used
    • User: Username for database
    • Password: The user password for the database.

• Class 2.DriverManager: The Driver manager class for managing all registered drivers.

  • Registerdriver (Driver): Registering a Driver class object
  • Connection getconnection (Url,uer,password): Gets the connected object whose return value is the Connection object

• 3.Connection interface: A Connection object that represents a Java program and a database, and this interface indicates that the connection to the database has been established

  • Createstatement: Creating Statement objects
  • PreparedStatement (): Create PreparedStatement Object
  • CallableStatement preparecall (): Create a CallableStatement object

• 4.Statement interface: Used to execute a static SQL statement and return its results

  • Executeupdate (String sql): Executing a static update SQL statement (DDL, DML)
  • ExecuteQuery (String sql): Executing a static query SQL statement

• 5.PreparedStatement Interface:

  • int executeupdate (): Perform Precompiled update SQL statements (DDL, DML)
  • ResultSet executeQuery (): Execute a Precompiled query SQL statement (DQL)

• 6.ResultSet interface: Used to encapsulate the queried data, representing the result set

  • Next (): Move the cursor to the next line
  • GETXX (): Gets the value of the column

Basic application of JDBC