mysql database connection step method and its instance in javaweb
- 2020-07-21 07:41:30
- OfStack
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