What is JDBC

The JDBC (Java Data Base Connectivity,java database connection) is a Java API for executing SQL statements that provides unified access to a variety of relational databases, which are used by a set of The Java language is composed of classes and interfaces. JDBC provides a benchmark to build more advanced tools and interfaces that enable database developers to write database applications, while JDBC is a trademark name.

A: JDBC simple use first, JDBC is a collection of interfaces, is used to control SQL statements, to import some packages, this example is used by JUnit. Testthe test method

Import Java.sql.connection;import java.sql.drivermanager;import Java.sql.resultset;import java.sql.Statement;// Test method, the method must be public void, plus a @Test, and then check the method, right-run as Junitimport Org.junit.test;public class Demo {@Test//Send INSERT statement public void Fun1 () throws EXCEPTION{//1 Import driver Class Library//2 Register driver Drivermanager.registerdriver (new Com.mysql.jdbc.Driver ());//3 Connection database Connection conn = Drivermanager.getconnection ("jdbc:mysql://localhost:3306/day05", "root", "7q7q77qq");//4 Operational database Statement St = Conn.createstatement (); String sql = "INSERT into ' t_user '" + "VALUES (NULL, ' Tom ', ')"; st.executeupdate (sql);//5 close Resource St.close (); Conn.close () ;} @Test//Send query statement public void Fun2 () throws EXCEPTION{//1 Import driver Class Library//2 Register driver Drivermanager.registerdriver (new Com.mysql.jdbc.Driver ()); 3 Connect to the database, the last day05 represents the meaning of the database connection conn = Drivermanager.getconnection ("jdbc:mysql://localhost:3306/day05", " Root "," 7q7q77qq ");//4 operation Database Statement st = Conn.createstatement (); String sql = "SELECT * from T_user"; ResultSet rs = st.executequery (sql);//TraversalThe contents of the result set and print while (Rs.next ()) {String name = rs.getstring ("name"), int id = rs.getint ("id"), int age = Rs.getint ("Age"); System.out.println (name+ "==>" +age+ "==>" +id);} 5 Close resource st.close (); Conn.close ();}}

Second, the JDBC driver registration is recommended to use the next method, the first is called in a static code block, static code block is called when the class is loaded, so we load the class can be

Package Cn.itcast.b_dm;import Java.sql.connection;import Java.sql.drivermanager;import java.sql.ResultSet;import Java.sql.statement;import Org.junit.test;//drivermanager details public class Demo {@Testpublic void Fun1 () throws exception{ Registration Drive//Registration Method 1: Not recommended = = drive the implementation of static code in the class and called//Drivermanager.registerdriver (driver);//Registration Method 2: Recommended Class.forName (" Com.mysql.jdbc.Driver ");} @Testpublic void Fun2 () throws exception{//get the connection drivermanager.getconnection ("jdbc:mysql://127.0.0.1:3306/day05", " Root "," 1234 ");//url full format:  Large Protocol: Sub-Protocol://IP Address: Port number/library name? parameter key = parameter value//complete:  jdbc:mysql://127.0.0.1:3306/day05? useunicode=true&characterencoding=utf8//simple: <span style= "White-space:pre" ></span>  jdbc:mysql :///day05?useunicode=true&characterencoding=utf8}}

Iii.: JDBC Four modes of execution

Package Cn.itcast.c_st;import Java.sql.connection;import Java.sql.drivermanager;import java.sql.ResultSet;import Java.sql.statement;import org.junit.test;//statement details public class Demo {@Test//execute original, additions and deletions can be returned value true=>  Query has result set | false=> query No result set//executebatch bulk execution sql//executeupdate perform additions/deletions//executequery execute query public void fun1 () throws exception{// 1 Registered driver Class.forName ("Com.mysql.jdbc.Driver");//2 get connection Connection conn = Drivermanager.getconnection ("jdbc:mysql:// Localhost:3306/day05 "," root "," 7q7q77qq ");//3 Create Statementstatement st = conn.createstatement ()//4 write SqlString sql =" I Nsert into ' t_user ' + ' VALUES (NULL, ' Jerry ', 16) ';//5 execution SqlBoolean result = St.execute (SQL); SYSTEM.OUT.PRINTLN (result);//false//6 close Resource st.close (); Conn.close ();} @Testpublic void Fun2 () throws EXCEPTION{//1 registered drive Class.forName ("Com.mysql.jdbc.Driver");//2 get connection Connection conn = Drivermanager.getconnection ("jdbc:mysql://localhost:3306/day05", "root", "7q7q77qq");//3 Create Statementstatement st = Conn.createstatemENT ();//4 write SqlString sql = "SELECT * from T_user";//5 execute SqlBoolean result = St.execute (SQL); if (result) {ResultSet rs = St.getresultset (); System.out.println (RS);} 6 Close resource st.close (); Conn.close ();} @Test//executeupdatepublic void Fun3 () throws EXCEPTION{//1 registered driver Class.forName ("Com.mysql.jdbc.Driver");//2 Get connected Connection conn = Drivermanager.getconnection ("jdbc:mysql://localhost:3306/day05", "root", "7q7q77qq");//3 Create Statementstatement st = Conn.createstatement ();//4 write SqlString sql = "INSERT into ' t_user '" + "VALUES (NULL, ' Jack ', 20) ";//5 executes sqlint row = st.executeupdate (sql), if (row!=1) {throw new RuntimeException (" Insert failed! ");} System.out.println (row);//1//6 Close Resource st.close (); Conn.close ();} @Test//executequerypublic void Fun4 () throws EXCEPTION{//1 registered driver Class.forName ("Com.mysql.jdbc.Driver");//2 Get connected Connection conn = Drivermanager.getconnection ("jdbc:mysql://localhost:3306/day05", "root", "7q7q77qq");//3 Create Statementstatement st = Conn.createstatement ();//4 write SqlString sql = "SELECT * FROM t_user ";//5 execution SqlResultSet rs = st.executequery (sql);//Traverse rsSystem.out.println (RS);//6 Close Resource st.close (); Conn.close ();}}

Iv.: JDBC modifies the database below using the Updatestring method to modify the

Package Cn.itcast.d_rs;import Java.sql.connection;import Java.sql.drivermanager;import java.sql.ResultSet;import Java.sql.statement;import org.junit.test;//resultset Details//2. Result set Reverse modification database public class Demo3 {@Testpublic void fun1 () Throws EXCEPTION{//1 registered driver Class.forName ("Com.mysql.jdbc.Driver");//2 get connection Connection conn = Drivermanager.getconnection ("jdbc:mysql://localhost:3306/day05", "root", "1234"),//3 create statementstatement st = Conn.createstatement (resultset.type_scroll_insensitive,resultset.concur_updatable);//4 writing SqlString sql =  " SELECT * from T_user ";//5 execute SqlResultSet rs = st.executequery (sql);//Use result set to reverse Modify Database Rs.next ();//move cursor to first row rs.updatestring ("Name", "Tom");//Modify the value of the first row name column to Chinese Tom Rs.updaterow ();//Confirm Modify//6 Close resource st.close (); Conn.close ();}}

The tool class in JDBC uses the tool class to import data from the db.properties to obtain information such as a user name password, and ultimately the process of getting connected

Package Cn.itcast.e_tool;import Java.io.fileinputstream;import Java.io.inputstream;import java.sql.Connection; Import Java.sql.drivermanager;import java.sql.resultset;import java.sql.sqlexception;import java.sql.Statement;  Import Java.util.properties;public class Jdbcutils {private static string driver;private static string Url;private static String User;private static string Password;static{try {//0 Read config file properties prop = new properties (); InputStream is = new FileInputStream ("Src/db.properties");p rop.load (IS), Is.close ();d river = Prop.getproperty ("driver"); URL = Prop.getproperty ("url"), user = Prop.getproperty ("user");p Assword = prop.getproperty ("password");//1 Registered driver Class.forName (driver);} catch (Exception e) {e.printstacktrace ();}} 1 Get connection public static Connection getconnection () {Connection conn = null;try {//2 GET connection conn = drivermanager.getconnection (U RL, user, password);} catch (Exception e) {e.printstacktrace (); throw new RuntimeException ("Create Connection Failed!");} Return conn;} 2 releasing the resource//1> parameter may be empty//2> Call the Close method to throw an exception to ensure that even if an exception occurs, you can continue to close the//3> close order, from small to large public static void close (Connection conn, Statement St, ResultSet RS) {try {if (rs!=null) {Rs.close ()}}} catch (SQLException e) {e.printstacktrace ();} Finally{try {if (st!=null) {St.close ()}} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();} Finally{try {if (conn!=null) {Conn.close ()}} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();}}}} public static void Main (string[] args) {System.out.println (getconnection ());}}

VI.: SQL injection issues SQL injection, by inserting a SQL command into a Web form to submit or entering a query string for a domain name or page request, eventually achieves a malicious SQL command that deceives the server. The FUN1 () below is a SQL injection problem that fun2 the problem.

Package Cn.itcast.f_ps;import Java.sql.connection;import Java.sql.preparedstatement;import java.sql.ResultSet; Import Java.sql.statement;import Org.junit.test;import Cn.itcast.e_tool. Jdbcutils;public class Demo {@Test//Demo using statement object, SQL injection problem public void fun1 () throws exception{string name = "xxx ' OR 1=1- - "; String password = "7Q7Q77QQ";//1 Gets the connection connection conn= jdbcutils.getconnection ();//2 get Statementstatement st = Conn.createstatement ();//3 assemble SQL statement String sql = "SELECT * from T_user WHERE name= '" +name+ "' and password= '" +password+ "; //4 executes the SQL and gets the result resultset rs = st.executequery (sql);//5 determines if the login succeeds if (Rs.next ()) {System.out.println ("Login succeeded!");} ELSE{SYSTEM.OUT.PRINTLN ("Login failed!");} 6 Close Resource Jdbcutils.close (conn, St, RS);} @Test//demo using Preparestatement object, solve SQL injection problem public void fun2 () throws exception{string name = "xxx ' OR 1=1--"; string password = "7q7q77qq";//1 get Connection Connection conn= jdbcutils.getconnection ();//2 assemble SQL statement String sql = "SELECT * FROM T_us Er WHERE name=? and password=? "; /3 Get PreparestAtementpreparedstatement PS = conn.preparestatement (SQL),//4 set parameter to PS object in ps.setstring (1, name);p s.setstring (2, password);//5 shipping parameters, execute SQL and get results resultset rs = ps.executequery ();//5 determine if login succeeded if (Rs.next ()) {System.out.println (" Login successful! ");} ELSE{SYSTEM.OUT.PRINTLN ("Login failed!");} 6 Close Resource Jdbcutils.close (conn, PS, RS);}}

The last PreparedStatement is a bit of a problem and is being worked out.

What is JDBC