mysql database connection step method and its instance in javaweb

1. Connect directly and do not package into the tool class. Main steps:

Pilot package: ES3en-ES4en-ES5en-5.0.8en-ES6en. jar(click to jump to the download interface), put it under WebRoot/ ES9en-ES10en /lib/

1. The load driver / / com. MySQL. jdbc. Driver

2. Get the connection Connection object

3. Gets the Statement object used to send SQL to the database

4. Execute sql to obtain and parse data

5. Close the connection and release the resource

/* Protocol: Sub-protocol :// The host : port / The database name */
Stringurl="jdbc:mysql://localhost:3306/jdbctest";
//mysql User name and password of the database, set by yourself when installing, 1 As the default for root
Stringuser="root";
Stringpassword="root";
Connectionconnection=null;
Statementstatement=null;
ResultSetresultSet=null;
try{
//1. The load driver //com.mysql.jdbc.Driver
/*
*DriverManager.registerDriver(new
*Driver()); In this way, the driver is loaded twice, which means two are created drive object 
*/
Class.forName("com.mysql.jdbc.Driver");
//2. Get connected 
connection=DriverManager.getConnection(url,user,password);
//3. Get is used to send to the database SQL the Statement object 
statement=connection.createStatement();
//4. perform sql, To get the data 
resultSet=statement.executeQuery("SELECT*FROMusers;");
// Analytical data 
while(resultSet.next()){
intid=resultSet.getInt("id");
Stringname=resultSet.getString("name");
Stringpsd=resultSet.getString("password");
Stringemail=resultSet.getString("email");
Stringbirthday=resultSet.getString("birthday");
System.out.println(id+""+name+""+psd+""+email
+""+birthday);
}
}catch(ClassNotFoundExceptione){
e.printStackTrace();
}catch(SQLExceptione){
e.printStackTrace();
}finally{
//5. Close the connection and release the resource 
if(resultSet!=null){
try{
resultSet.close();
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
resultSet=null;
}
if(statement!=null){
try{
statement.close();
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
statement=null;
}
if(connection!=null){
try{
connection.close();
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
connection=null;
}
/*  Protocol: Sub-protocol :// The host : port / The database name  */
String url = "jdbc:mysql://localhost:3306/jdbctest";
// mysql User name and password of the database, set by yourself when installing, 1 As the default for root
String user = "root";
String password = "root";
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
  // 1. The load driver //com.mysql.jdbc.Driver
  /*
   * DriverManager.registerDriver(new
   * Driver()); In this way, the driver is loaded twice, which means two are created drive object 
   */
  Class.forName("com.mysql.jdbc.Driver");
  // 2. Get connected 
  connection = DriverManager.getConnection(url, user, password);
  // 3. Get is used to send to the database SQL the Statement object 
  statement = connection.createStatement();
  // 4. perform sql, To get the data 
  resultSet = statement.executeQuery("SELECT * FROM users;");
  //  Analytical data 
  while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String psd = resultSet.getString("password");
String email = resultSet.getString("email");
String birthday = resultSet.getString("birthday");
System.out.println(id + " " + name + " " + psd + " " + email
+ " " + birthday);
  }
} catch (ClassNotFoundException e) {
  e.printStackTrace();
} catch (SQLException e) {
  e.printStackTrace();
} finally { 
    //5. Close the connection and release the resource 
  if (resultSet != null) {
try {
  resultSet.close();
} catch (SQLException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
}
resultSet = null;
  }
  if (statement != null) {
try {
  statement.close();
} catch (SQLException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
}
statement = null;
  }
  if (connection != null) {
try {
  connection.close();
} catch (SQLException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
}
connection = null;
  }
}

2. Encapsulate the database connection as a utility class

The advantage of doing this is that in real development, you can do this, and you can change the whole world just by changing one place.

1. Create a configuration file named ES37en.properties for src/

url=jdbc:mysql://localhost:3306/jdbctest
username=root
password=root
driver=com.mysql.jdbc.Driver

2. Tools:

importjava.io.IOException;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
importjava.util.Properties; 
publicclassJdbcUtil{
// Private static variables that are used to read configuration files 
privatestaticPropertiesconfig=newProperties();
static{
try{
// Configure resource files 
config.load(JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties"));
// The load driver 
Class.forName(config.getProperty("driver"));
}catch(IOExceptione){
e.printStackTrace();
}catch(ClassNotFoundExceptione){
e.printStackTrace();
}
}
publicstaticConnectiongetConnection(){
Connectionconnection=null;
try{
connection=DriverManager.getConnection(config.getProperty("url"),config.getProperty("username"),config.getProperty("password"));
}catch(SQLExceptione){
e.printStackTrace();
}
returnconnection;
}
// To close a connection and release resources 
publicstaticvoidreleaseConn(Connectionconnection,Statementstatement,
ResultSetresultSet){
if(resultSet!=null){
try{
resultSet.close();
}catch(SQLExceptione){
e.printStackTrace();
}
resultSet=null;
}
if(statement!=null){
try{
statement.close();
}catch(SQLExceptione){
e.printStackTrace();
}
statement=null;
}
if(connection!=null){
try{
connection.close();
}catch(SQLExceptione){
e.printStackTrace();
}
connection=null;
}
} 
}

3. Application Examples:

Connectionconnection=null;
Statementstatement=null;
ResultSetresultSet=null;
try{
// Call a static method in the utility class to get the connection 
connection=JdbcUtil.getConnection();
statement=connection.createStatement();
resultSet=statement.executeQuery("select*fromusers");
while(resultSet.next()){
intid=resultSet.getInt("id");
Stringname=resultSet.getString("name");
Stringpsd=resultSet.getString("password");
Stringemail=resultSet.getString("email");
Stringbirthday=resultSet.getString("birthday");
System.out.println(id+""+name+""+psd+""+email
+""+birthday); 
}
}catch(Exceptione){
e.printStackTrace();
}finally{
// Call the static method in the utility class to close the connection and release the resource 
JdbcUtil.releaseConn(connection,statement,resultSet);
}

I hope this article can be helpful to friends in need