MySQL数据库连接配置（tomcat，java）

 

 

 

■Mysql数据库

drop database messageBoard;
create database messageBoard DEFAULT CHARACTER SET utf8;

use MessageBoard;

create table messageboard_user(
id int not null auto_increment primary key,
muId varchar(16) NOT NULL,
muMessage varchar(100) NOT NULL,
muReply varchar(100),
muTime TIMESTAMP NOT NULL,
muState varchar(15),
muIp varchar(20) NOT NULL
)Engine=MyISAM
DEFAULT CHARACTER SET utf8;

create table login_user(
luId varchar(16) NOT NULL primary key,
luPass varchar(50) NOT NULL,
luPermission char(1)NOT NULL
)Engine=MyISAM
DEFAULT CHARACTER SET utf8;

create table basemessage(
bmId char(16) NOT NULL primary key,
bmNum char(9) NOT NULL,
bmName varchar(12)NOT NULL,
bmEMail varchar(60) NOT NULL,
bmSex char(3) NOT NULL
)Engine=MyISAM
DEFAULT CHARACTER SET utf8;


create table countAccessNum(
count int 
)Engine=MyISAM
DEFAULT CHARACTER SET utf8;

insert into countAccessNum values(0);
insert into login_user values('admin','admin','3');

 

■Tomcat中数据源配置

tomcat/conf/context.xml

数据库后面指定编码，可以防止乱码

<Resource name="jdbc/messageBoard"
auth="Container"
type="javax.sql.DataSource"
maxTotal="10"
maxIdle="3"
maxWaitMillis="10000"
username="root"
password="root001"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/messageboard?useUnicode=true&amp;characterEncoding=UTF-8" />

-----

 

 

■添加mysql连接驱动的jar包

放入tomcat的「lib」中

下载地址

https://mvnrepository.com/artifact/mysql/mysql-connector-java
https://mvnrepository.com/artifact/mysql/mysql-connector-java/5.1.47

点击下面的 jar（983KB）直接下载

如果jar版本过低，会出现下面的错误
AbstractMethodError: com.mysql.jdbc.Connection.isValid(I)Z

 

■java代码

import javax.sql.DataSource;
import javax.naming.InitialContext;
import java.sql.Connection;
public class ConnectionFactory {
	public  static Connection  getConnection() throws Exception{
		Connection cn = null;
		InitialContext ctx =null;
		try{
			ctx = new InitialContext();
			DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/messageBoard");
			cn = ds.getConnection();
			System.out.println("使用纯JDBC获取连接成功");
		}
		catch(Exception e)
		{
				System.out.println("使用纯JDBC获取连接失败 ,   "+e.getMessage());
				throw new Exception("使用纯JDBC获取连接失败");
		}
		finally
		{
						ctx.close();	
		}//final end
		return cn;
	}
	
	
}

----

import java.sql.*;
import java.util.List;

import com.city.sxzlc.messageboard.dao.IUserMessageDao;
import com.city.sxzlc.messageboard.value.UserMessageValue;
import com.city.sxzlc.factory.*;
public class UserMessageDaoImpl implements IUserMessageDao {

	Connection cn;
	public boolean create(UserMessageValue message) throws Exception {
		
		
		//muId varchar(16),
		//muMessage varchar(100),
		//muTime datetime,
		//muIp char(15),
		String sql ="insert into messageboard_user(muId,muMessage,muTime,muIp,muState) values(?,?,?,?,'未回复')";
		cn = ConnectionFactory.getConnection();
		try{
			PreparedStatement pst = cn.prepareStatement(sql);
			pst.setString(1, message.getMuID());
			pst.setString(2, message.getMuMessage());
			pst.setDate(3, new java.sql.Date(  message.getMuTime().getTime()  )    );
			pst.setString(4, message.getMuIP());
			pst.execute();
			System.out.println("用户留言成功插入数据库");
			
		}
		catch(Exception e)
		{
			throw new Exception("留言板数 用户 据插入时错误"+e.getMessage());
			
		}
		finally{
			cn.close();
		}
				
		
		return true;
	}

	
	public ResultSet read()throws Exception{
		String sql ="select * from messageboard_user";
		Statement st = null;
		ResultSet rs = null;
		cn = ConnectionFactory.getConnection();
		try{
			 st = cn.createStatement();
			 rs = st.executeQuery(sql);
			 
			System.out.println("用户留言从数据库取出成功");
			
		}
		catch(Exception e)
		{
			throw new Exception("留言板数据 用户 取出时错误"+e.getMessage());
			
		}
		finally{
			//cn.close(); 此方法的cn不应该在这里关闭， 调用他的方法要关闭cn  问题001
		}
		return rs;
	}
	//问题 001 解决方法
	public void closeConnection()throws Exception{
		cn.close();
	}
}

---