步骤一:导包
步骤二、连接数据库配置信息
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://db3.vanteam.cn:33306/xxx
user=root
password=123456
步骤三、创建Java连接MySQL工具类
package com.vanteam.utlis;
import java.io.IOException;
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.sql.Statement;
import java.util.Properties;
public class JDBCUtilsConfig {
private static Connection con;
private static String driverClass;
private static String url;
private static String user;
private static String password;
static {
try {
readConfig();
Class.forName(driverClass);
} catch (Exception e) {
throw new RuntimeException("数据库连接失败");
}
}
private static void readConfig() throws IOException {
InputStream is = JDBCUtilsConfig.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pro = new Properties();
pro.load(is);
driverClass = pro.getProperty("driverClass");
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
}
public static String getDataBaseName() {
return url.substring(url.lastIndexOf('/')+1, url.length());
}
public static Connection getConnection() {
try {
con = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
public static void close(Connection con, PreparedStatement pstm, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException ex) {
}
}
if (pstm != null) {
try {
pstm.close();
} catch (SQLException ex) {
}
}
if (con != null) {
try {
con.close();
} catch (SQLException ex) {
}
}
}
public static void close(Connection con, Statement stat, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException ex) {
}
}
if (stat != null) {
try {
stat.close();
} catch (SQLException ex) {
}
}
if (con != null) {
try {
con.close();
} catch (SQLException ex) {
}
}
}
}
###步骤四:使用工具类
public class DataBaseUtils {
static Connection con=null;
static PreparedStatement pstm=null;
static ResultSet rs=null;
//单例模式:防止在其他类中实例化该对象
private static DataBaseUtils instance=new DataBaseUtils();
private DataBaseUtils() {
}
public static DataBaseUtils getDataBaseUtils() {
return instance;
}
/**
* 获取数据库所有的表名
* @return
*/
public static List<String> getTableNames(){
List<String> tableNames=new ArrayList<String>();
con=JDBCUtilsConfig.getConnection();
String sql="select table_name from information_schema.tables where table_schema=?";
try {
pstm= con.prepareStatement(sql);
pstm.setString(1, JDBCUtilsConfig.getDataBaseName());
rs=pstm.executeQuery();
while(rs.next()) {
String name=rs.getString("table_name");
tableNames.add(name);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtilsConfig.close(con, pstm, rs);
}
return tableNames;
}
/*
* 模糊查询
*/
public static <T> List<T> getDatasByLike(String TableName,Class<T> clazz,String keyword,List<String> columns) throws InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchMethodException, SecurityException{
List<T> list=new ArrayList<T>();
con=JDBCUtilsConfig.getConnection();
String sql="select * from "+TableName;
StringBuilder builder=new StringBuilder();
builder.append(sql);
if(null!=columns) {
for(int i=0;i<columns.size();i++) {
if(i==0) {
builder.append(" where "+columns.get(i)+" like ?");
}else{
builder.append(" or "+columns.get(i)+" like ?");
}
}
}
//" where name like ? or category like ? ";
try {
pstm=con.prepareStatement(builder.toString());
if(null!=columns) {
for(int i=0;i<columns.size();i++) {
pstm.setString(i+1,"%"+keyword +"%");
}
}
rs=pstm.executeQuery();
//遍历整个结果集,封装到集合中,每个元素是一个对象
while(rs.next()) {
//每条记录封装成一个对象,创建一个对象
T obj=clazz.getConstructor().newInstance();
//先得到实体类中有哪些属性
Field[] fields = clazz.getDeclaredFields();
for(Field field:fields) {
//私有的要暴力
field.setAccessible(true);
//列名=属性名
String name = field.getName();
//要赋值的对象,值
field.set(obj, rs.getObject(name)); //从结果集中获取数据
}
list.add(obj);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtilsConfig.close(con, pstm, rs);
}
return list;
}
}