原来一直用tomcat,最近想用java直接连接mysql却发现连不上,费了半天的劲才发现问题所在
现在把代码贴出来和大家分享一下下:
首先是安装环境的配置,可以参照我以前的一篇文章<> ,现在我们所需要做的就是把mysql的驱动程序mysql-connector-java-5.0.4-bin.jar放到C:/Program Files/Java/jre1.5.0_09/lib/ext目录下,并设置环境变量classpath添加C:/Program Files/Java/jre1.5.0_09/lib/extmysql-connector-java-5.0.4-bin.jar
好的现在我们就来查看一下是否能够连接:
我的mysq配置是user--root;password--851120,测试数据库用的是内带的mysql
首先是一个简单的测试程序:
import java.sql.*;
public class Test1 {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection cOnn= null;
try {
cOnn= getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if (cOnn== null)
System.out.println("error!");
else
System.out.println("ok!");
conn.close();
} catch (SQLException e) { // TODO Auto-generated catch
e.toString();
}
}
public static Connection getConnection() throws SQLException {
String dbURL = "jdbc:mysql://localhost:3306/mysql";
String dbUser = "root";
String dbPassword = "851120";
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return DriverManager.getConnection(dbURL, dbUser, dbPassword);
}
}
显示结果为"ok!",说明已经建立连接
下面我们来进行一个简单的查询(这是我在做一个练习时用的,所以是原版代码,你只要注意连接数据库和释放连接的代码就可以了)
package com.qiudawei115.mysql.MysqlDatabaseBean
/**
* 包含所有项目的公共类
*/
package com.qiudawei115.mysql;
import java.sql.*;
import javax.naming.*;
/**
* @author Administrator
* 使用静态方法
* 获取数据库连接
* 返回最大主键值
* 关闭数据库连接
*/
public class MysqlDatabaseBean {
/**
* 空构造函数
*/
public MysqlDatabaseBean() {
// TODO Auto-generated constructor stub
}
/**
* 获取数据库连接
* 测试成功
* @throws NamingException
* jndiName是数据源的名字
*/
public static Connection getConnection(String dbURL,String dbUser,String dbPassword)throws SQLException{
try{
Class.forName("com.mysql.jdbc.Driver");
}catch(ClassNotFoundException e){
e.printStackTrace();
}
return DriverManager.getConnection(dbURL,dbUser,dbPassword);
}
/**
* 根据表明获取某个表的最大主键
* @param conn
* @param tableName
* @return
* 经过测试没有问题可以使用
*/
public synchronized static int getMaxId(Connection conn,String tableName)throws SQLException{
int nMaxId=0;
Statement stmt=conn.createStatement();
String sql="select maxid from tableseq where tablename='"+tableName+"'";
ResultSet rs=null;
if(stmt.execute(sql)){
/**
* 得到执行语句后的结果集合
*/
rs=stmt.getResultSet();
while(rs.next()){
nMaxId=rs.getInt(1);
}
}
if(nMaxId>0){
/**
* 该表存在最大主键值
*/
nMaxId++;
sql="update tableseq set maxid="+nMaxId +" where tablename='"+tableName+"'";
}else{
nMaxId=1;
sql="insert into tableseq(tablename,maxid) values('"+tableName+"',"+nMaxId+")";
}
stmt.executeUpdate(sql);
stmt.close();
return nMaxId;
}
/**
* 关闭连接释放资源
*/
public synchronized static void close(Connection conn,Statement stmt,ResultSet rs){
try{
if(rs!=null)
rs.close();
}catch(SQLException e){
e.printStackTrace();
}
try{
if(stmt!=null)
stmt.close();
}catch(SQLException e){
e.printStackTrace();
}
try{
if(conn!=null)
conn.close();
}catch(SQLException e){
e.printStackTrace();
}
}
}
com.qiudawei115.model.DatabaseBean
/**
* 包含本工程所需要的javabean
*/
package com.qiudawei115.model;
import java.sql.*;
import com.qiudawei115.mysql.*;
/**
* @author Administrator
* 数据库连接操作
*/
public class DatabaseBean {
/**
* 空构造函数
*/
public DatabaseBean() {
// TODO Auto-generated constructor stub
}
/**
* 获取数据库连接
* 测试成功
* @throws NamingException
* @throws NamingException
*/
public static Connection getConnection()throws SQLException{
return MysqlDatabaseBean.getConnection("jdbc:mysql://localhost:3306/mysql","root","851120");
}
/**
* 获取最大主键值
* @throws NamingException
*/
public static int getMaxId(String tableName)throws SQLException{
Connection cOnn=getConnection();
int maxId=MysqlDatabaseBean.getMaxId(conn, tableName);
conn.close();
return maxId;
}
/**
* 关闭连接
*/
public static synchronized void close(Connection conn,Statement stmt,ResultSet rs){
MysqlDatabaseBean.close(conn, stmt, rs);
}
/**
* 执行数据库更新
* 成功返回true
* 失败返回false
* @throws NamingException
*/
public static boolean update(String sql){
Connection cOnn=null;
Statement stmt=null;
try{
cOnn=getConnection();
stmt=conn.createStatement();
int nResult=stmt.executeUpdate(sql);
return nResult==1;
}catch(SQLException e){
e.printStackTrace();
return false;
}finally{
close(conn,stmt,null);
}
}
}
测试类:
import com.qiudawei115.model.DatabaseBean;
import java.sql.*;
public class Test {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection cOnn=null;
Statement stmt=null;
ResultSet rs=null;
try {
cOnn=DatabaseBean.getConnection();
if(conn!=null)
System.out.println("建立连接");
stmt=conn.createStatement();
rs=stmt.executeQuery("select name from help_keyword");
while(rs.next())
System.out.println(rs.getString("name"));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DatabaseBean.close(conn, stmt, rs);
}
}
运行结果:
建立连接
<>
ACTION
ADD
AES_ENCRYPT
AFTER
AGAINST
AGGREGATE
ALL
ALTER
AND
ARCHIVE
AS
ASC
AUTOCOMMIT
AVG_ROW_LENGTH
BACKUP
BDB
BEFORE
BEGIN
BERKELEYDB
BETWEEN
BIGINT
BINARY
BINLOG
BOOL
BOOLEAN
BOTH
BTREE
BY
BYTE
CACHE
...
注意如果发生这个错误:
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:
** BEGIN NESTED EXCEPTION **
java.net.SocketException
MESSAGE: java.net.SocketException: Socket is not connected: connect
STACKTRACE:
java.net.SocketException: java.net.SocketException: Socket is not connected: connect
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:156)
at com.mysql.jdbc.MysqlIO.(MysqlIO.java:276)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2666)
at com.mysql.jdbc.Connection.(Connection.java:1531)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at Test1.getConnection(Test1.java:43)
at Test1.main(Test1.java:12)
** END NESTED EXCEPTION **
Last packet sent to the server was 31 ms ago.
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2741)
at com.mysql.jdbc.Connection.(Connection.java:1531)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at Test1.getConnection(Test1.java:43)
at Test1.main(Test1.java:12)
解决的办法是再开始菜单中启动mysql,输入密码,启动信息为
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 18 to server version: 5.0.22-community-nt
Type 'help;' or '/h' for help. Type '/c' to clear the buffer.
mysql>
在进行连接就没事了,具体原因我也不太清楚
到此为止,收工!
有问题可在此留言
京公网安备 11010802041100号