关于MySQL的基础的四种操作
亲手封装的工具类
package com.wkk.util;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
public class DBUtil {
/**
* 默认链接
*
* @return
*/
public static Connection getConnection() {
return getConnection("localhost", "3306", "wkk", "root", "");
}
/**
* @param ip
* @param port
* @param dbName
* @param user
* @param password
* @return
*/
public static Connection getConnection(String ip, String port, String dbName, String user, String password) {
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(
"jdbc:mysql://" + ip + ":" + port + "/" + dbName + "?useUnicode=true&characterEncoding=utf8", user, password);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
/**
* 执行sql语句
*
* @param sql
* @return
*/
public static boolean executeSQL(String sql) {
Connection connection = getConnection();
boolean result = false;
try {
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
statement.close();
result = true;
} catch (SQLException e) {
e.printStackTrace();
result = false;
}
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
/**
* 获取唯一ID
*
* @return
*/
public static String getID() {
return UUID.randomUUID().toString().replace("-", "");
}
/**
* 根据sql装载对象
*
* @param sql
* @param class_
*/
public static Object loadObject(String sql, Class class_) {
Connection connection = getConnection();
Object object = null;
try {
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
object = load(resultSet, class_);
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return object;
}
/**
* 装载集合
*
* @param sql
* @param class_
* @return
*/
public static List loadArray(String sql, Class class_) {
Connection connection = getConnection();
List<Object> list = new ArrayList<>();
try {
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
Object object = load(resultSet, class_);
list.add(object);
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
private static Object load(ResultSet resultSet, Class class_) {
Object object = null;
try {
object = class_.newInstance();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
if (object == null)
return null;
ResultSetMetaData resultSetMetaData = null;
try {
resultSetMetaData = resultSet.getMetaData();
} catch (SQLException e) {
e.printStackTrace();
}
int columnCount = 0;
try {
columnCount = resultSetMetaData.getColumnCount();
} catch (SQLException e) {
e.printStackTrace();
return null;
}
Field fields[] = class_.getDeclaredFields();
Map<String, Field> map = new HashMap<>();
for (Field field : fields) {
map.put(field.getName().toUpperCase(), field);
}
for (int i = 0; i < columnCount; i++) {
try {
String name = resultSetMetaData.getColumnName(i + 1);
Field field = map.get(name.toUpperCase());
if (field == null) {
continue;
}
String simpleName = field.getType().getSimpleName();
Method vMethod = ResultSet.class.getDeclaredMethod("get" + simpleName.substring(0, 1).toUpperCase() + simpleName.substring(1), int.class);
Object value = vMethod.invoke(resultSet, i + 1);
Method method = class_.getDeclaredMethod("set" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1), field.getType());
method.invoke(object, value);
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
return object;
}
/**
* 获取单一值
*/
public static Object getValue(String sql) {
Connection connection = getConnection();
Object object = null;
try {
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
object = resultSet.getObject(1);
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return object;
}
/**
* 批量操作
*/
public static boolean batchUpdate(String sql, int count, BatchSetter batchSetter) {
Connection connection = DBUtil.getConnection();
try {
connection.setAutoCommit(false);
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < count; i++) {
batchSetter.setValues(preparedStatement,i);
preparedStatement.addBatch();
}
preparedStatement.executeBatch();
connection.commit();
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
/**
* 批处理设置值的接口
*/
public interface BatchSetter {
void setValues(PreparedStatement preparedStatement, int i);
}
}
1.创建一个测试表
create table user(
userID varchar(32) ,
name varchar(30),
phone varchar(11),
password varchar(30),
createTime datetime,
primary key(userID)
)charset=utf8 ;
2.对应的实体类
package com.wkk.entity;
public class User {
private String userID;
private String name;
private String phone;
private String password;
private String createTime;
public String getUserID() {
return userID;
}
public void setUserID(String userID) {
this.userID = userID;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getCreateTime() {
return createTime;
}
public void setCreateTime(String createTime) {
this.createTime = createTime;
}
@Override
public String toString() {
return "User{" +
"userID='" + userID + '\'' +
", name='" + name + '\'' +
", phone='" + phone + '\'' +
", password='" + password + '\'' +
", createTime='" + createTime + '\'' +
'}';
}
}
3.对数据库操作的接口和实现类
package com.wkk.entity.dao;
import com.wkk.entity.User;
import java.util.List;
public interface UserDao {
/**
* 创建
*
* @param user
* @return
*/
boolean createUser(User user);
/**
* 删除
*
* @param userID
* @return
*/
boolean delete(String userID);
/**
* 修改名称
*
* @param userID
* @param name
* @return
*/
boolean updateUserName(String userID, String name);
/**
* 根据用户ID查询
*
* @param userID
* @return
*/
User getUser(String userID);
/**
* 获取user表中总记录数
*
* @return
*/
long getCount();
/**
* 随机获取一条数据
*
* @return
*/
User getRandomUser();
/**
* 获取全部
* @return
*/
List<User> getAll();
}
package com.wkk.entity.dao.impl;
import com.wkk.db.DBUtil;
import com.wkk.entity.User;
import com.wkk.entity.dao.UserDao;
import java.util.List;
public class UserDaoImpl implements UserDao {
@Override
public boolean createUser(User user) {
String sql = "insert into user (userID,name,phone,password,createTime) value " +
"('" + DBUtil.getID() + "','" + user.getName() + "','" + user.getPhone() + "','" + user.getPassword() + "',now());";
return DBUtil.executeSQL(sql);
}
@Override
public boolean delete(String userID) {
String sql = "delete from user where userID='" + userID + "';";
return DBUtil.executeSQL(sql);
}
@Override
public boolean updateUserName(String userID, String name) {
String sql = "update user set name='" + name + "' where userID='" + userID + "';";
return DBUtil.executeSQL(sql);
}
@Override
public User getUser(String userID) {
String sql = "select * from user where userID='" + userID + "' ;";
return (User) DBUtil.loadObject(sql, User.class);
}
@Override
public long getCount() {
String sql = "select count(*) from user;";
return (long) DBUtil.getValue(sql);
}
@Override
public User getRandomUser() {
String sql = "select * from user order by rand() limit 1;";
return (User) DBUtil.loadObject(sql, User.class);
}
@Override
public List<User> getAll() {
String sql = "select * from user";
return DBUtil.loadArray(sql, User.class);
}
}
4.测试
package com.wkk.db;
import com.wkk.entity.User;
import com.wkk.entity.dao.UserDao;
import com.wkk.entity.dao.impl.UserDaoImpl;
import java.util.List;
import java.util.Random;
public class Test {
public static void main(String agr[]) {
UserDao userDao = new UserDaoImpl();
//1.初始化10条数据
for (int i = 0; i < 10; i++) {
User user = new User();
user.setPassword("888888");
user.setPhone(getPhone());
user.setName(getName());
userDao.createUser(user);
}
//2.查询总记录条数
System.out.println(userDao.getCount());
//3.随机查询一条数据
User user = userDao.getRandomUser();
System.out.println(user);
//4.删除
userDao.delete(user.getUserID());
System.out.println(userDao.getCount());
//5.修改
user = userDao.getRandomUser();
System.out.println(user);
userDao.updateUserName(user.getUserID(), "李四");
//6.查询
user = userDao.getUser(user.getUserID());
System.out.println(user);
System.out.println();
//7.查询全部
List<User> list = userDao.getAll();
for (User user1 : list) {
System.out.println(user1);
}
}
public static String getName() {
String name = "";
for (int i = 0; i < 2; i++) {
name += new String(new char[]{(char) (new Random().nextInt(20902) + 19968)});
}
return name;
}
public static String getPhone() {
String phone = "";
for (int i = 0; i < 11; i++) {
phone += String.valueOf((int) (Math.random() * 10));
}
return phone;
}
}
运行结果:
10
User{userID='055226a0de1548e9abb6bb130ad56373', name='姀莵', phone='72417557386', password='888888', createTime='2018-01-10 12:18:32.0'}
9
User{userID='510831be2c3c4a40a157ae378834d474', name='燔蠭', phone='94456920210', password='888888', createTime='2018-01-10 12:18:32.0'}
User{userID='510831be2c3c4a40a157ae378834d474', name='李四', phone='94456920210', password='888888', createTime='2018-01-10 12:18:32.0'}
User{userID='0284a7411c63489d85ac73145b18aad5', name='雠鉘', phone='71624562102', password='888888', createTime='2018-01-10 12:18:32.0'}
User{userID='0e95276060fb402a9de985df4623d648', name='辜腭', phone='58807577008', password='888888', createTime='2018-01-10 12:18:32.0'}
User{userID='181b27acfb474933a92a279e4c6eda78', name='聿飜', phone='08291959176', password='888888', createTime='2018-01-10 12:18:32.0'}
User{userID='1a3d161ebcc842e99d51eaffa05a3173', name='鞛釚', phone='39505832540', password='888888', createTime='2018-01-10 12:18:32.0'}
User{userID='2aae35c5ff094aecbde56f7ab76e3402', name='燍薈', phone='58669312637', password='888888', createTime='2018-01-10 12:18:32.0'}
User{userID='2d29f73665b44d6a98f377345437c1a7', name='妀秛', phone='69024250227', password='888888', createTime='2018-01-10 12:18:32.0'}
User{userID='510831be2c3c4a40a157ae378834d474', name='李四', phone='94456920210', password='888888', createTime='2018-01-10 12:18:32.0'}
User{userID='976960c0a7ea418ca976d472af67a3e1', name='笣叡', phone='50958385645', password='888888', createTime='2018-01-10 12:18:32.0'}
User{userID='af860f1e12e84014bd2f2d4f8c7bd26e', name='漕鑛', phone='49590492862', password='888888', createTime='2018-01-10 12:18:32.0'}
Process finished with exit code 0
欢迎交流,与君共勉