jdbc Connection Database Instance Details
- 2021-06-29 10:56:51
- OfStack
Introduction to JDBC
JDBC is called Java Data Base Connectivity (java Database Connection), which provides all-in-one access to many databases.JDBC is a set of database access programming interface developed by sun and is an API of SQL level.It is written in the java language, so it has good cross-platform characteristics. Database applications written with JDBC can run on any platform that supports java without having to write different applications on different platforms.
JDBC programming steps
(1) Loading drivers:
Download driver package: http://dev.mysql.com/downloads/connector/j/
Unzip to get the jar file.Copy the file to the Java project directory Java Resources/Libraries/buildpath.
(2) Get a database connection
(3) Create an Statement object:
(4) Send SQL command to database
(5) Processing returns from databases (ResultSet class)
package com.baidu.emp.jdbcTest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import com.mysql.jdbc.Driver;
/**
* Start using jdbc Connect to database
* @author Admin
*
*/
public class Test001 {
public static void main(String[] args) throws Exception {
/**
* Load Driver
*/
// Method 1 :
/*
* import java.sql.DriverManager; import com.mysql.jdbc.Driver;
*/
// Driver driver = new Driver();
// DriverManager.registerDriver(driver);
// Method 2 : ( Recommended Use )
Class.forName("com.mysql.jdbc.Driver");
/**
* create link
*/
String url = "jdbc:mysql://localhost:3306/testjdbc";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
// Establish statement object
Statement statement = connection.createStatement();
/**
* implement SQL , get the result set
*/
String sql = "select * from test01";
ResultSet result = statement.executeQuery(sql);
// Traversing result set
while (result.next()) {
String name = result.getString("name");
int id = result.getInt("id");
System.out.println(name + "\t" + id);
}
/**
* Close links, release resources
*/
result.close();
statement.close();
connection.close();
}
}
Prevent SQL injection from switching to prepareStatement
package com.boya.emp.jdbcTest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* SQL Injection, use prepareStatement Object precompilation
* @author Admin
*
*/
public class Test002 {
public static void main(String[] args) throws Exception {
/**
* Load Driver
*/
Class.forName("com.mysql.jdbc.Driver");
/**
* create link
*/
String url = "jdbc:mysql://localhost:3306/testjdbc";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
// write SQL
String sql = "select * from test01 where id = ?";
// Establish statement Object, precompiled
PreparedStatement statement = connection.prepareStatement(sql);
// Setting parameters
statement.setInt(1, 2);
/**
* implement SQL , get the result set
*/
ResultSet result = statement.executeQuery();
// Traversing result set
while (result.next()) {
String name = result.getString("name");
int id = result.getInt("id");
System.out.println(name + "\t" + id);
}
/**
* Close links, release resources
*/
result.close();
statement.close();
connection.close();
}
}
Code optimization, configuration files, tool classes, add or delete this check
Adding configuration files makes it easy to modify the database and log on to users.
jdbc.properties (Profile Name)
driverName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/testjdbc
userName=root
password=root
Note that no spaces, quotes, etc. are allowed in the middle of writing the configuration file
Tool class: Enhanced code reuse
package com.baidu.emp.utils;
import java.io.InputStream;
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 org.junit.Test;
public class JdbcUtils {
static String driverClassName;
static String url;
static String user;
static String password;
static {
// Create Profile Object
Properties properties = new Properties();
// Load profile input stream
InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
// Reload Configuration File
try {
properties.load(inputStream);
// Get the value of the configuration file
driverClassName = properties.getProperty("driverName");
url = properties.getProperty("url");
user = properties.getProperty("userName");
password = properties.getProperty("password");
Class.forName(driverClassName);
} catch (Exception e) {
// throw
throw new RuntimeException(e);
}
}
/**
* Get Connections
*/
@Test
public void testName() throws Exception {
System.out.println(driverClassName);
}
public static Connection getConnection() {
Connection connection = null;
try {
connection = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
// throw
throw new RuntimeException(e);
}
return connection;
}
/**
* Close links, release resources
*/
public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
}
resultSet = null; // Clean up garbage in time
// Be careful not to end up in an endless loop
close(connection, statement);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* Add, delete, release resources
*/
public static void close(Connection connection, PreparedStatement statement) {
try {
if (connection != null) {
connection.close();
}
connection = null;
if (statement != null) {
statement.close();
}
statement = null;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
Test additions and deletions check:
package com.baidu.emp.jdbcTest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.baidu.emp.utils.JdbcUtils;
/**
* Use jdbcUtils Connect to database for add-delete check
*
* @author Admin
*
*/
public class Test003 {
// initialize value
Connection connection = null;
PreparedStatement statement = null;
ResultSet result = null;
@Before
public void start() throws Exception {
// create link
connection = JdbcUtils.getConnection();
System.out.println(" create link ");
}
@After
public void end() throws Exception {
// Close Link
JdbcUtils.close(connection, statement, result);
System.out.println(" Close Link ");
}
/**
* insert data
* @throws Exception
*/
@Test
public void add() throws Exception {
String sql = "insert into test01 values(null,?)";
statement = connection.prepareStatement(sql);
statement.setString(1, " plum 4");
int result = statement.executeUpdate();
if (result!=0) {
System.out.println(" Added Successfully ");
}
}
/**
* Delete data
* @throws Exception
*/
@Test
public void del() throws Exception {
String sql = "delete from test01 where id =?";
statement = connection.prepareStatement(sql);
statement.setInt(1,3);
int result = statement.executeUpdate();
if (result!=0) {
System.out.println(" Delete succeeded ");
}
}
/**
* Modify data
* @throws Exception
*/
@Test
public void change() throws Exception {
String sql = "update test01 set name = ? where id = ?";
statement = connection.prepareStatement(sql);
statement.setString(1, " Zhang Fei ");
statement.setInt(2, 2);
int result = statement.executeUpdate();
if (result!=0) {
System.out.println(" Successful modification ");
}
}
/**
* Query all data
* @throws Exception
*/
@Test
public void findAll() throws Exception {
String sql = "select id , name from test01";
statement = connection.prepareStatement(sql);
result = statement.executeQuery();
if (result.next()) {
System.out.println(" query was successful ");
}
}
/**
* Conditional Query Data
* @throws Exception
*/
@Test
public void findOne() throws Exception {
String sql = "select id , name from test01 where id = ?";
statement = connection.prepareStatement(sql);
statement.setInt(1, 2);
result = statement.executeQuery();
if (result.next()) {
System.out.println(" query was successful ");
}
}
}
These are the relevant knowledge and codes, thank you for your support for this site.