JAVA--JDBC connecting MySQL

JDBC

JDBC(java Data Base connectivity,java database connection) is a Java APIfor executing SQL statements. You can provide unified access to a variety of relational databases, consisting of a set of classes and interfaces written in the Java language. is the standard specification for Java access to databases

JDBC provides a benchmark to build more advanced tools and interfaces that enable database developers to write database applications.

JDBC requires a connection driver, the driver is the two devices to communicate, to meet a certain communication data format, data format by the device provider, the device provider for the device to provide driver software, through the software can communicate with the device.

JDBC is an interface, the driver is the implementation of the interface, no driver will not be able to complete the database connection, thus unable to operate the database! Each database vendor needs to provide its own driver to connect to its own company's database, which means that the driver is typically provided by the database generator vendor.

steps for JDBC Operations database

1. Registration Driver

• Tell the JVM which database driver to use

2. Get the connection

• Use the classes in JDBC to complete the connection to the MySQL database

3. Get the statement execution platform

• To get the performer object for the SQL statement through the Connection object

4. Execute SQL statements

• Executes SQL statements to the database using the performer object, and gets the results after the execution of the database

5. Processing results

6. Releasing resources a bunch of close ()

1. Increase and revise operation

public static void Main (string[] args) throws classnotfoundexception,sqlexception{// Registerdriver parameters are passed in the MySQL driver implementation class//drivermanager.registerdriver (new Driver ());//But look at the MySQL driver class source code, also made a registration, This will register 2 times the driver, so we need to use the reflection technology//1. Register Drive-Reflection technology, add driver class to content//Use Java.sql.DriverManager class static method Registerdriver (Driver Driver) Class.forName ("Com.mysql.jdbc.Driver");//2. Obtaining a database connection DriverManager a static method in a class//static Connection getconnection (String URL , string user, string password)//return value is the implementation class of the connection interface, in the MySQL driver//url: Database address jdbc:mysql://Connection host IP: Port number//database name String URL = "Jdbc:mysql://localhost:3306/xingedb"; String username= "root"; String password= "xxx"; Connection conn = drivermanager.getconnection (URL, username, password);//3. Gets the statement execution platform, obtains the performer object to the SQL statement through the database connection object// The Conn object calls the method Statement createstatement () Gets the Statement object, sends the SQL statement to the database//return value is the implementation class object of the Statement interface, and in the MySQL driver Statement stat = Conn.createstatement ();//4. Execute SQL statement//Execute SQL statement through the Performer object call method, get result//int executeupdate (String sql) Execute SQL statement in database, insert Delete update//return value int, Operation success data table how many rows int row = stat.executeupdate ("INSERT into sort (sname,sprice,sdesc) VALUES (' Auto supplies ', 50000, ' crazy price rise ')"); System.out.println (row);//6. Release resources A bunch of close () Stat.close (); Conn.close ();}

2. Query Operation

The query is using the ExecuteQuery method

public static void Main (string[] args) throws exception{//1. Register driver Class.forName ("Com.mysql.jdbc.Driver");//2. Gets the connection object string url = "Jdbc:mysql://localhost:3306/mybase"; String username= "root"; String password= "123"; Connection conn = drivermanager.getconnection (URL, username, password)//3. Get Execute SQL Statement Object Statement Stat = Conn.createstatement ();//SqlString sql = "SELECT * from sort" of the spelling query;//4. Call the Performer object method, execute the SQL statement to get the result set//ResultSet executeQuery (String sql)  Execute SQL statement in the SELECT query//return value ResultSet interface of the implementation class object, Implementation class ResultSet rs = stat.executequery (sql) in MySQL driver;//5. Process result set//ResultSet interface Method Boolean next () returns true with the result set, Returns False if there is no result set while (Rs.next ()) {//Gets each column of data, using a method that is ResultSet interface Getxx method parameters, it is recommended to write String column name System.out.println (rs.getint ("Sid ") +"   "+rs.getstring (" sname ") +"   "" +rs.getdouble ("Sprice") + "   " +rs.getstring ("Sdesc");} Rs.close ();//query also close result set Stat.close (); Conn.close ();}

SQL injection Attacks

3. Solve SQL injection ProblemPreprocessing objects

When using PreparedStatement to preprocess an object, it is recommended that all actual arguments for each SQL statement be separated by commas

Inquire

private static void Func () throws ClassNotFoundException, SQLException {    class.forname ("Com.mysql.jdbc.Driver");    String url = "Jdbc:mysql://127.0.0.1:3306/testdb";    String username = "root";    String password = "5456";    Connection conn = drivermanager.getconnection (URL, username, password);        String sql = "SELECT * from users WHERE uname=?" and passwd=? ";  Use    a placeholder String uname = "a";    String passwd = "123456 ' OR ' 1";    Call the connection interface method Preparestatement, get Preparestatement interface implementation class    PreparedStatement PST =  Conn.preparestatement (SQL);    Pst.setobject (1,uname);     Call the PST object Set method and set the parameter    pst.setobject (2,PASSWD) on the question mark placeholder;    ResultSet rs = Pst.executequery ();    while (Rs.next ()) {        System.out.println (rs.getstring ("uname") + "   " +rs.getstring ("passwd"));}    }

Delete and change

public static void Main (string[] args) throws Exception{class.forname ("Com.mysql.jdbc.Driver"); String url = "Jdbc:mysql://localhost:3306/mybase"; String username= "root"; String password= "123"; Connection con = drivermanager.getconnection (URL, username, password);//spelling modified SQL statement, Parameters adopted? placeholder String sql = "UPDATE sort SET S Name=?,sprice=? WHERE sid=? "; /Call Database Connection object Con method preparestatement Gets the precompiled object of the SQL statement PreparedStatement PST = con.preparestatement (SQL);//method of calling PST Setxxx settings The placeholder pst.setobject (1, "Car Beauty");p St.setobject (2, 49988);p St.setobject (3, 7);//Call PST method to execute SQL statement pst.executeupdate (); Pst.close (); Con.close ();}

JAVA--JDBC connecting MySQL