JDBC PreparedStatement, CallableStatement, and transactions, rollback example

classes, files, jars used in the program

Code:

1. File: Db.properties file contents

User=root
Password=123
url=jdbc:mysql:///student_db
Driver=com.mysql.jdbc.driveraaa

2, Class Utils.class

Import Java.io.InputStream;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.SQLException;
Import java.util.Properties;

public class Utils {

private static Properties Properties=null;
Static
{
Properties=new Properties ();
Try (InputStream in=utils.class.getresourceasstream ("/db.properties");) {
Properties.load (in);
String driver=properties.getproperty ("Driver");
Load the driver here, the rule of law multiple loads
Class.forName (driver);

}
catch (Exception e) {
}
}

public static Connection getconnection ()
{
String url=properties.getproperty ("url");
String user=properties.getproperty ("user");
String password=properties.getproperty ("password");
try {
Return drivermanager.getconnection (URL, user, password);
} catch (SQLException e) {
TODO Auto-generated catch block
E.printstacktrace ();
}
return null;

}
This can be used to close the resource, by sending a reference
public static void Close ()
{

}
}

3, class Jdbcdome, primarily for testing stored procedures, and functions

Import java.sql.CallableStatement;
Import java.sql.Connection;
Import java.sql.SQLException;
Import Java.sql.Types;

Import Com.jdbc.utils.Utils;

public class Jdbcdome {

public static void Main (string[] args) {
TODO auto-generated method stubs

Connection Con=null;
CallableStatement Call=null;

try {
Con=utils.getconnection ();
The following tests the transaction, rolling back
Con.setautocommit (FALSE);
String sql= "{call Tea_pro (?,?,?)}"; /Stored Procedures
Call=con.preparecall (SQL);
Call.setint (a);
Call.setint (2,2);
Call.registeroutparameter (3,types.char);
Call.execute ();
System.out.println (Call.getstring (3));//Get JDBC in the form of a String in the Java programming language


Function
Sql= "{? =call Teache_info2 (?,?)}";
Call=con.preparecall (SQL);
Call.registeroutparameter (1, Types.char);
Call.setint (2, 1);
Call.setint (3, 1);
Call.execute ();
System.out.println (call.getstring (1));
The value of the CHAR, VARCHAR, or LongVarChar parameter.

Con.commit ();
Con.rollback ();
} catch (SQLException e) {
TODO Auto-generated catch block
E.printstacktrace ();
}
}

}

4, this class is used for testing PreparedStatement, transactions, etc., in order to make the program easy, here another main function to test

Import java.sql.Connection;
Import Java.sql.Date;
Import java.sql.PreparedStatement;
Import java.sql.SQLException;
Import Com.jdbc.utils.Utils;

public class Sessiondome {

/**
* @param args
*/
public static void Main (string[] args) {
TODO auto-generated method stubs

Connection Con=null;
PreparedStatement Statement=null;

try {
Getting connections from Utils
Con=utils.getconnection ();
Open transaction
Con.setautocommit (FALSE);
(1002, ' John Doe ', ' female ', ' 1970-01-21 ', ' Haidian District, Beijing '),
Statement=con.preparestatement ("INSERT into teacher values (?,?,?,?,?,?)");
Statement.setint (1, 37);
Statement.setint (2, 2111);
Statement.setstring (3, "Li Liu");
Statement.setstring (4, "male");
Statement.setdate (5, New Date (2001,1,1));
Statement.setstring (6, "China");
Statement.executeupdate ();

Statement=con.preparestatement ("Delete from teacher where num=1003");
Statement.executeupdate ();
Con.rollback ();//Rollback
Con.commit ();//Commit a transaction

} catch (SQLException e) {
TODO Auto-generated catch block
E.printstacktrace ();
}

}

}

JDBC PreparedStatement, CallableStatement, and transactions, rollback example