Database and Java Connection (JDBC)

Why JDBC? : Because SQL statements can access the database directly and Java cannot directly access

Jdbcjava developers are APIs that are interfaces to database providers

The JDBC drive is divided into 4 types depending on how it is implemented:

1. Jdbc-odbc Bridge plus ODBC Driver:jdbc-odbc Bridge driver to convert JDBC calls to ODBC calls. (This combination provides JDBC access via ODBC drivers.) ODBC binary Code--and In many cases, database client code--must is loaded on all client machine that uses a JDBC-ODBC Br Idge. Sun provides a jdbc-odbc Bridge driver, which is appropriate for experimental use and for situations in which no other DRI Ver is available.) Note that ODBC binaries, in many cases including database client code, must be loaded onto each client that uses the driver. This type of driver is best suited to the Enterprise network (where the client installation is not a major problem), or the application server code for a three-tier architecture written in Java.

2. Native-api Partly-java driver: translates the JDBC call into a call to the database client API. (A Native-api partly Java technology-enabled driver:this type of driver converts JDBC calls into calls on the client API For Oracle, Sybase, Informix, DB2, or other DBMS. Note that as the bridge driver, this style is driver requires that some binary code is loaded on each client machine.) This type of driver translates the JDBC calls on the client API into calls to Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this type of driver requires that some binary code be loaded onto each client.


3. Pure Java Driver for database middleware: first convert the JDBC call to the Dbms-independent network protocol, then the server-side middleware is converted to the network protocol that the database server can receive. (Net-protocol fully Java technology-enabled driver #This style of driver translates JDBC calls into the middleware vendor ' s protocol, which is then translated to a DBMS protocol by a middleware server. The middleware provides connectivity to many different databases.) This kind of Web server middleware can connect its pure Java client to many different databases. The specific protocol used depends on the provider. Typically, this is the most flexible JDBC driver. It is possible that all providers of this solution provide products suitable for use in the intranet. In order for these products to also support Internet access, they must handle the additional requirements of the web for security, access through firewalls, and so forth. Several providers are adding JDBC drivers to their existing database middleware products.

4. Direct-to-database Pure Java Driver: Converts a JDBC call directly to a network protocol that a specific database server can receive. (Native-protocol fully Java technology-enabled driver #This style of driver converts JDBC calls into the network protocol Used directly by DBMSs, allowing a direct call from the client machine to the DBMS server and providing a practical soluti On for intranet access.) This allows the DBMS server to be called directly from the client machine and is a useful workaround for intranet access. Since many of these protocols are dedicated, the database provider itself will be the main source, and several providers are already doing it.

Package com.wode.test;

Import Java.io.FileInputStream;
Import java.io.IOException;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.sql.Statement;
Import java.util.Properties;

/***
*
* @author Administrator
* Find data in a database
*/

public class Seek {

public static void Main (string[] args) {
First get the information in the configuration file
Properties Properties = new properties ();
try {
Properties.load (New FileInputStream ("./sql.properties"));
String name = Properties.getproperty ("name");
String pwd = Properties.getproperty ("pwd");
String url = properties.getproperty ("url");
Get the drive through reflection
Class.forName ("Com.mysql.jdbc.Driver");
Ready to connect
Connection conn = drivermanager.getconnection (URL,NAME,PWD);
SYSTEM.OUT.PRINTLN (conn); See if the connection is successful
Prepare SQL statement to remove all accounts and passwords and user information from Qqusers
String sql = "SELECT * from Qqusers s join Qquserinfo o where s.qquser_id=o.qquser_id";
Run the SQL statement
Statement Statement = Conn.createstatement ();
ResultSet ResultSet = statement.executequery (sql);
Remove results
while (Resultset.next ()) {
String qqname = resultset.getstring ("S.qquser_name");
String qqpwd = resultset.getstring ("S.qquser_pwd");
String qqinfoname = resultset.getstring ("O.qqinfo_nick");
int qqinfoage = Resultset.getint ("O.qqinfo_age");
System.out.println ("Account:" +qqname+ "Password:" +qqpwd+ "nickname:" +qqinfoname
+ "Age:" +qqinfoage);
}
} catch (IOException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (ClassNotFoundException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}
}
}

Package com.wode.test;

Import Java.io.FileInputStream;
Import java.io.FileNotFoundException;
Import java.io.IOException;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.util.Properties;
Import Java.util.Scanner;

/**
*
* @author Administrator
* Simulated QQ User Login
*/
public class Landing {

public static void Main (string[] args) {
Find what we need in the config file
Properties Properties = new properties ();
try {
Properties.load (New FileInputStream ("./sql.properties"));
String name = Properties.getproperty ("name");
String pwd = Properties.getproperty ("pwd");
String url = properties.getproperty ("url");
Reflective Drive
Class.forName ("Com.mysql.jdbc.Driver");
Connecting to a database
Connection Connection = drivermanager.getconnection (URL,NAME,PWD);
User Input account password
Scanner Scanner = new Scanner (system.in);
System.out.println ("Please enter your account");
String uName = Scanner.next ();
System.out.println ("Please enter password");
String upwd = Scanner.next ();
Preparing SQL statements
String sql = "Select COUNT (*) from qqusers where qquser_name=? and qquser_pwd=? ";
Precompiled SQL statements using Statement here can be a big risk that someone else could destroy the database in a special way.
So we use preparedstatement here to pass the SQL statement into the precompiled
Only static SQL statements will use Statement and dynamic SQL statements must use PreparedStatement
PreparedStatement ptmt = connection.preparestatement (sql);
Ptmt.setstring (1, uName);//The 1 here means the first one? The subscript of the number
Ptmt.setstring (2, upwd);
Receive results
ResultSet ResultSet = Ptmt.executequery ();
if (Resultset.next ()) {//If there is a next entry if
System.out.println (Resultset.getint (1));//getint (1) access to the result labeled 1

}
int a = Resultset.getint (1);
if (a!=1) {
System.out.println ("The account or password you entered is incorrect");
}else if (a==1) {
SYSTEM.OUT.PRINTLN ("Login Successful");
}

} catch (FileNotFoundException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (IOException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (ClassNotFoundException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}
}
}

Package com.wode.test;

Import Java.io.FileInputStream;
Import java.io.FileNotFoundException;
Import java.io.IOException;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.sql.Statement;
Import java.util.Properties;

/**
*
* @author Administrator
* Increase the data of the database
*/
public class Increase {

public static void Main (string[] args) {
Get information about the configuration file first
Properties Properties = new properties ();
try {
Properties.load (New FileInputStream ("./sql.properties"));
String name = Properties.getproperty ("name");
String pwd = Properties.getproperty ("pwd");
String url = properties.getproperty ("url");
Reflective Drive
Class.forName ("Com.mysql.jdbc.Driver");
Ready to connect
Connection Connection = drivermanager.getconnection (URL,NAME,PWD);
Preparing SQL statements
String sql = "INSERT into qqusers values (null, ' 789 ', ' 123 ', 1)";
Run the SQL statement
Statement Statement = Connection.createstatement ();
Using the int type to accept the result if it is 1 then proves that running successfully is 0 means the database has not changed
int resultSet = statement.executeupdate (sql);
System.out.println (ResultSet);
} catch (FileNotFoundException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (IOException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (ClassNotFoundException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}
}
}

Package com.wode.test;

Import Java.io.FileInputStream;
Import java.io.FileNotFoundException;
Import java.io.IOException;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.sql.Statement;
Import java.util.Properties;

Import Javax.imageio.stream.FileImageInputStream;

/**
*
* @author Administrator
* Delete data from the database
*/
public class Deletedb {

public static void Main (string[] args) {
Config file Find what we need
Properties Properties = new properties ();
try {
Properties.load (New FileInputStream ("./sql.properties"));
String name = Properties.getproperty ("name");
String pwd = Properties.getproperty ("pwd");
String url=properties.getproperty ("url");
Reflective Drive
Class.forName ("Com.mysql.jdbc.Driver");
Ready to connect
Connection Connection = drivermanager.getconnection (URL,NAME,PWD);
Preparing SQL statements
String sql = "DELETE from qqusers where qquser_id=7";
Run the SQL statement
Statement Statement = Connection.createstatement ();
int resultSet = statement.executeupdate (sql);
System.out.println (ResultSet);
} catch (FileNotFoundException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (IOException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (ClassNotFoundException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}
}
}

Package com.wode.test;

Import Java.io.FileInputStream;
Import java.io.FileNotFoundException;
Import java.io.IOException;
Import java.security.acl.Permission;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.SQLException;
Import java.sql.Statement;
Import java.util.Properties;

/***
*
* @author Administrator
* Change the data in the database
*/
public class Change {

public static void Main (string[] args) {
Find what we need in the config file
Properties Properties = new properties ();
try {
Properties.load (New FileInputStream ("./sql.properties"));
String name = Properties.getproperty ("name");
String pwd = Properties.getproperty ("pwd");
String url = properties.getproperty ("url");
Reflective Drive
Class.forName ("Com.mysql.jdbc.Driver");
Connecting to a database
Connection Connection = drivermanager.getconnection (URL,NAME,PWD);
Preparing SQL statements
String sql = "UPDATE qqlog SET qqlog_time= ' 2016-09-09 00:00:00 ' WHERE qqlog_id=2";
Run the SQL statement
Statement Statement = Connection.createstatement ();
int resultSet = statement.executeupdate (sql);
System.out.println (ResultSet);
} catch (FileNotFoundException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (IOException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (ClassNotFoundException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}
}
}

Is my profile sql.properties

Name:root
Pwd:qwe
Url:jdbc:mysql://127.0.0.1:3306/qq

Batch processing:

Conn.setautocommit (FALSE); Cancel Auto-Commit

Statement stm = Conn.cerate Statement ();

Stm.addbatch (); Batch processing statements

Stm.executebatch (); Execute Batch processing statements

Conn.commit (); Manually submit

Conn.setautocommit (TRUE); Finally, the auto-commit is turned on

Database and Java Connection (JDBC)