Mysql database operation using JDBC

01? 2022-02-13 07:30:19 阅读数:760

mysql database operation using jdbc

The methods of connecting and closing the database are encapsulated in jdbcuntil Class

// Operating the database , Connect to database
public class jdbcuntil {
// Connect to database
public static Connection conn() throws ClassNotFoundException, IOException, SQLException {
InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(resourceAsStream);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
// Close the database
public static void Close(Connection connection, PreparedStatement preparedStatement){
try {
if (preparedStatement!=null){
preparedStatement.close();
}
if (connection!=null){
// close resource
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
// Query closed
public static void Close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
try {
if (preparedStatement!=null){
preparedStatement.close();
}
if (connection!=null){
// close resource
connection.close();
}
if(resultSet!=null){
resultSet.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}

Encapsulate the method of operating the database in BaseDao In class , Methods include adding, deleting, modifying and checking , And the calculation of the number of data , Maximum date of birth query  

// Encapsulation method
public abstract class BaseDao<T> {
// obtain customer class
private Class<T> clazz=null;
{
Type genericSuperclass = this.getClass().getGenericSuperclass();
ParameterizedType parameterizedType= (ParameterizedType) genericSuperclass;
Type[] actualTypeArguments = parameterizedType.getActualTypeArguments();
clazz= (Class<T>) actualTypeArguments[0];
}
// General addition, deletion and modification
public int udpate(Connection conn , String sql, Object... args) {
PreparedStatement preparedStatement = null;
try {
conn = jdbcuntil.conn();
preparedStatement = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1,args[i]);
}
// Return to delete several records
return preparedStatement.executeUpdate();
//preparedStatement.execute();
} catch (ClassNotFoundException | IOException | SQLException e) {
e.printStackTrace();
} finally {
jdbcuntil.Close(null,preparedStatement);
}
return 0;
}
// The query returns an object
public T searchsql(Connection conn,String sql,Object...a){
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
conn = jdbcuntil.conn();
preparedStatement = conn.prepareStatement(sql);
for (int i = 0; i <a.length ; i++) {
preparedStatement.setObject(i+1,a[i]);
}
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
if (resultSet.next()){
T t = clazz.newInstance();
for (int i = 0; i <columnCount; i++) {
// Get column values
Object object = resultSet.getObject(i + 1);
// To get the column name
//String columnName = metaData.getColumnName(i + 1);
// Get the alias of the column
String columnLabel = metaData.getColumnLabel(i + 1);
// By reflection
Field declaredField = clazz.getDeclaredField(columnLabel);
declaredField.setAccessible(true);
declaredField.set(t,object);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
jdbcuntil.Close(null,preparedStatement,resultSet);
}
return null;
}
// Query multiple results , General query record
public List<T> searchMany(Connection conn, String sql, Object...a){
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
conn = jdbcuntil.conn();
preparedStatement = conn.prepareStatement(sql);
for (int i = 0; i <a.length ; i++) {
preparedStatement.setObject(i+1,a[i]);
}
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
// Create set
ArrayList<T> ts = new ArrayList<>();
while (resultSet.next()){
T t = clazz.newInstance();
for (int i = 0; i <columnCount; i++) {
// Get column values
Object object = resultSet.getObject(i + 1);
// To get the column name
//String columnName = metaData.getColumnName(i + 1);
// Get the alias of the column
String columnLabel = metaData.getColumnLabel(i + 1);
// By reflection
Field declaredField = clazz.getDeclaredField(columnLabel);
declaredField.setAccessible(true);
declaredField.set(t,object);
}
// Add multiple objects to the collection
ts.add(t);
}
return ts;
} catch (Exception e) {
e.printStackTrace();
} finally {
jdbcuntil.Close(null,preparedStatement,resultSet);
}
return null;
}
// General method for querying special values
public <E> E getValue(Connection conn,String sql,Object ...a) {
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = conn.prepareStatement(sql);
for (int i = 0; i < a.length; i++) {
preparedStatement.setObject(i+1,a[i]);
}
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
return (E) resultSet.getObject(1);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
jdbcuntil.Close(null,preparedStatement,resultSet);
}
return null;
}
}

customer Class encapsulates user attributes ( Create a class based on the properties of the database )

public class customer {
private int id;
private String name;
private String email;
private Date birth;
public customer(){}
public customer(int id, String name, String email, Date date) {
this.id = id;
this.name = name;
this.email = email;
this.birth = date;
}
@Override
public String toString() {
return "customer{" +
"id=" + id +
", name='" + name + '\'' +
", email='" + email + '\'' +
", date=" + birth +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getDate() {
return birth;
}
public void setDate(Date birth) {
this.birth = birth;
}
}

Create an interface

// Operation database method interface
public interface CustomerDao {
// take cust Add to database
void insert(Connection conn,customer cust);
// Delete data
void deleteByID(Connection conn,int id);
// Modifying data
void update1(Connection conn,customer cust);
// according to id Query the user
customer getCustomer(Connection conn,int id);
// Query multiple records in the table
List<customer> geAll(Connection conn);
// Return the number of data
Long getcount(Connection conn);
// Returns the maximum birthday in the data table
Date getBirth(Connection conn);
}

Implementation interface method , Provide use  

public class customerIMP extends BaseDao<customer> implements CustomerDao{
// insert data
@Override
public void insert(Connection conn, customer cust) {
String sql="insert into customers(name,email,birth)values(?,?,?)";
udpate(conn,sql,cust.getName(),cust.getEmail(),cust.getDate());
}
// Delete data
@Override
public void deleteByID(Connection conn, int id) {
String sql="delete from customers where id=?";
udpate(conn,sql,id);
}
// Modifying data
@Override
public void update1(Connection conn, customer cust) {
String sql="update customers set name=?,email=?,birth=? where id=?";
udpate(conn,sql,cust.getName(),cust.getEmail(),cust.getDate(),cust.getId());
}
// Find corresponding id The data of
@Override
public customer getCustomer(Connection conn, int id) {
String sql="select id,name,email,birth from customers where id=?";
customer searchsql1 = searchsql(conn, sql, id);
return searchsql1;
}
// Query all data in the table
@Override
public List<customer> geAll(Connection conn) {
String sql="select id,name,email,birth from customers";
List<customer> customers = searchMany(conn, sql);
return customers;
}
// Calculate the number of data in the table
@Override
public Long getcount(Connection conn) {
String sql="select count(*) from customers ";
return getValue(conn, sql);
}
// Query the largest birthday data
@Override
public Date getBirth(Connection conn) {
String sql="select max(birth) from customers";
return getValue(conn,sql);
}
}

copyright:author[01?],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130730173503.html