使用可视化工具
首先要下载mysql,网上也有很详细的教程。
由于我对数据库的各种命令不太熟悉,所以就使用了可视化工具Navicat,新建表、设置外键等关系都比较方便。在连接的时候记得要先打开MySQL服务,我刚开始就是因为这个连接不上。
设计数据表
我的数据库包含了以下数据表:
最重要的是image和user两个数据库
travelimage
:上传的图片,包括如下字段
外键关系如下
traveluser
:用户的数据表,包括如下字段
geocities
geocontinents
geocountries_regions
:
分别对应城市,洲和国家的数据,即上传的照片的地理位置
下面的两个数据表用于表示多对多关系
travelimagecomment
:用户对某张照片的评论
travelimagefavor
:收藏信息。一个用户可以收藏多张照片,一张照片可以被多人收藏,所以需要第三张数据表来表示
traveluserfriendship
:好友信息。包括从邀请开始到拒绝或者同意,都会储存一条信息在数据表中
当然数据库的设计取决于你要实现的功能
连接数据库
javaweb中使用jdbc连接数据库,它可以隐藏不同的数据库的实现,采用统一的接口操作数据库,降低了项目和数据库的耦合度。
首先需要下载所需jar包,添加到lib文件夹中
可以直接连接数据库,不过我使用了数据库连接池,用于提高性能。它们的区别在于前者是每次连接数据库都要打开连接,用完之后关闭连接,下次使用又要重新连接,如果数据库和代码部署在不同的服务器上就会效率很低。后者则采用数据库连接池,存放一定数量的连接在池中,需要的时候直接拿取,用完则还给连接池,供下一次的操作使用。(当然自己开发的项目没什么性能区别
前者可上网搜索教程,后者具体步骤如下:
- 添加jar包
需要在lib文件夹中添加c3p0-0.9.5.5.jar文件,自行上网搜索或者去我的git上面下载 - 在src文件夹下面新建c3p0-config.xml文件,内容如下
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<named-config name="travelSource">
<!-- user 和 password就是你自己的数据库用户和密码 -->
<property name="user">root</property>
<property name="password">123456</property>
<!--没有特别设置的话下面的代码可直接复制-->
<property name="jdbcUrl">jdbc:mysql://localhost:3306/travel?serverTimezone=UTC&autoReconnect=true</property>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<!-- 如果池中数据连接不够时一次增长多少个 -->
<property name="acquireIncrement">5</property>
<!-- 初始化数据库连接池时连接的数量 -->
<property name="initialPoolSize">20</property>
<!-- 数据库连接池中的最大的数据库连接数 -->
<property name="maxPoolSize">25</property>
<!-- 数据库连接池中的最小的数据库连接数 -->
<property name="minPoolSize">5</property>
</named-config>
</c3p0-config>
- 创建jdbc工具类
因为c3p0是数据安全的,所以可以用static
,也应该这样,不然如果一个程序有多个连接池的话就没有性能提升的意义了。
package jdbcUtils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcUtils {
private static DataSource dataSource;
static {
dataSource = new ComboPooledDataSource("travelSource");
}
public static DataSource getDataSource() {
return dataSource;
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static void release(Connection connection, Statement statement, ResultSet resultSet){
if(connection != null){
try {
connection.close();
}
catch (Exception e){
e.printStackTrace();
}
}
if(statement != null){
try {
statement.close();
}
catch (Exception e){
e.printStackTrace();
}
}
if(resultSet != null){
try {
resultSet.close();
}
catch (Exception e){
e.printStackTrace();
}
}
}
}
操作数据库
采用了DAO模式,即data access object,将数据库操作封装起来,后端代码通过调用object的method来实现增删改查。
一般来说一个数据表对应一个实体类,并且对每个属性都提供get和set方法,(想要了解更多可去学习Javabean相关知识),然后再对应一个操作类,继承Dao基类,提供操作相关方法,如图
Dao的实现如下:
package dao;
import domain.Picture;
import jdbcUtils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class Dao<T> {
private QueryRunner queryRunner = new QueryRunner();
private Class<T> clazz;
public Dao(){
Type superClass = getClass().getGenericSuperclass();
if(superClass instanceof ParameterizedType){
ParameterizedType parameterizedType = (ParameterizedType)superClass;
Type[] types = parameterizedType.getActualTypeArguments();
if(types != null && types.length > 0){
if(types[0] instanceof Class){
clazz = (Class<T>) types[0];
}
}
}
}
public <E> E getForValues(String sql, Object ... args){
Connection connection = null;
try {
connection = JdbcUtils.getConnection();
return (E)queryRunner.query(connection,sql,new ScalarHandler(),args);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,null,null);
}
return null;
}
public <E> List<E> getAllValues(String sql, Object ... args){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List<Object> entities = new ArrayList<>();
try {
connection = JdbcUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for(int i = 1; i <= args.length; i++){
preparedStatement.setObject(i,args[i-1]);
}
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
entities.add(resultSet.getObject(1));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,preparedStatement,resultSet);
}
return (List<E>) entities;
}
/**
*
* @param sql
* @param args
* @return true: success ,false:failure
*/
public boolean update(String sql, Object ... args){
Connection connection = null;
try {
connection = JdbcUtils.getConnection();
queryRunner.update(connection,sql,args);
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
JdbcUtils.release(connection,null,null);
}
}
public T get(String sql, Object ... args){
Connection connection = null;
try {
connection = JdbcUtils.getConnection();
return queryRunner.query(connection,sql,new BeanHandler<>(clazz),args);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,null,null);
}
return null;
}
public List<T> getAll(String sql, Object ... args){
Connection connection = null;
try {
connection = JdbcUtils.getConnection();
return queryRunner.query(connection,sql,new BeanListHandler<>(clazz),args);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,null,null);
}
return null;
}
}
举一个具体dao的例子,比如说user对应的dao
package dao;
import domain.User;
import java.util.List;
public class UserDao extends Dao<User>{
public String getPasswordByNameOrEmail(String emailOrName){
String sql = "SELECT Pass FROM traveluser WHERE UserName = ? OR Email = ?";
return getForValues(sql,emailOrName,emailOrName);
}
public User getUserByNameOrEmail(String emailOrName){
String sql = "SELECT Pass password, UID uid, UserName username, Email email, State state FROM traveluser WHERE UserName = ? OR Email = ?";
//System.out.println(user);
return get(sql,emailOrName,emailOrName);
}
public long getCountWithName(String name) {
String sql = "SELECT count(*) FROM traveluser WHERE UserName = ?";
return getForValues(sql,name);
}
public long getCountWithEmail(String email) {
String sql = "SELECT count(*) FROM traveluser WHERE Email = ?";
return getForValues(sql,email);
}
public boolean save(User user){
String sql = "INSERT INTO traveluser (UserName, Email, Pass) VALUES (?,?,?)";
return update(sql,user.getUsername(),user.getEmail(),user.getPassword());
}
public List<User> getUserByFuzzyUsername(String fuzzyUsername){
String sql = "SELECT UID uid, UserName username, Email email, State state FROM traveluser WHERE UserName LIKE ?";
return getAll(sql,"%"+fuzzyUsername+"%");
}
public User getUserByUid(int uid){
String sql = "SELECT UID uid, UserName username, Email email, State state FROM traveluser WHERE UID = ?";
return get(sql,uid);
}
public List<User> getFriendsByUid(int uid){
String sql = "SELECT u.UID uid, u.UserName username, u.State state FROM traveluserfriendship f, " +
"traveluser u ((f.inviterID = ? AND u.UID = f.inviteeID) OR (f.inviteeID = ? AND u.UID = f.inviterID)) AND f.state = 1;";
return getAll(sql,uid,uid);
}
public List<User> getWaitingInvitationByUid(int uid){
String sql = "SELECT f.inviteeID uid, u.UserName username, u.State state FROM traveluserfriendship f, " +
"traveluser u WHERE f.inviterID = ? AND u.UID = f.inviteeID AND f.state = 0;";
return getAll(sql,uid);
}
public List<User> getRefusedInvitationByUid(int uid){
String sql = "SELECT f.inviteeID uid, u.UserName username, u.State state FROM traveluserfriendship f, " +
"traveluser u WHERE f.inviterID = ? AND u.UID = f.inviteeID AND f.state = -1;";
return getAll(sql,uid);
}
public List<User> getInviteMeByUid(int uid){
String sql = "SELECT u.UID uid, u.UserName username, u.State state FROM traveluserfriendship f, traveluser u " +
"WHERE f.inviteeID = ? AND u.UID = f.inviterID AND f.state = 0;";
return getAll(sql,uid);
}
public boolean setState(int state,int uid){
String sql = "UPDATE traveluser SET State = ? WHERE UID = ?;";
return update(sql,state,uid);
}
}
我使用了queryrunner类来代替实现,当然可以利用反射等机制自己实现,其他的类也是根据自己需要去实现