jdbc连接demo java访问mysql

一、jdbc官方帮助

JDBC帮助：http://dev.mysql.com/doc/connector-j/en/

API参考：http://docs.oracle.com/javase/8/docs/api/java/sql/package-summary.html

二、maven配置mysql连接器

  <dependencies>
  <dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.0.5</version>
  <scope>runtime</scope>
  </dependency>
  </dependencies>

三、JDBC代码示例

下载地址：JDBCUtils.java

1 mysqld重启后，Connection需要重新连接。

2 违反数据库约束后会抛出MySQLIntegrityConstraintViolationException异常。

package mysql;

import java.sql.*;

public class JDBCUtils {

private Connection con;
private PreparedStatement ps;

static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (Exception e) {
e.printStackTrace();
}
}

public JDBCUtils() {
con = getConnection();
}

public void close() {
try {
if (con != null) {
con.close();
con = null;
}
} catch(SQLException e) {
e.printStackTrace();
}
}

private Connection getConnection(
String ip, String port, String db, String user, String password) {
Connection con = null;
String url = "jdbc:mysql://" + ip.concat(":") + port.concat("/") + db;
try {
con = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
return con;
}

private Connection getConnection() {
Connection con = getConnection(
"211.149.156.144", "3306", "test", "remote", "remote");
return con;
}

public ResultSet executeQuery(String sql) {
try {
ps = con.prepareStatement(sql);
return ps.executeQuery();
} catch(Exception e) {
e.printStackTrace();
}
return null;
}

public void closeQuery(ResultSet ret) {
try {
if (ret != null) {
ret.close();
ret = null;
}
if (ps != null) {
ps.close();
ps = null;
}
} catch(SQLException e) {
e.printStackTrace();
}
}

public void displayResultSet(ResultSet ret) {
try {
ResultSetMetaData meta = ret.getMetaData();
int cols = meta.getColumnCount();
while (ret.next()) {
for (int idx = 1; idx <= cols; ++idx) {
System.out.print(meta.getColumnName(idx) + "=");
System.out.print(ret.getObject(idx));
if (idx != cols)
System.out.print("\t");
}
System.out.println();
}
} catch(Exception e) {
e.printStackTrace();
}
}

public void querySample(String sql) {
ResultSet ret = executeQuery(sql);
displayResultSet(ret);
closeQuery(ret);
}

public int excuteUpdate(String sql) {
try {
ps = con.prepareStatement(sql);
int updateCnt = ps.executeUpdate();
ps.close();
return updateCnt;
} catch(Exception e) {
e.printStackTrace();
}
return 0;
}

public static void main(String[] args) {
int updateCnt;
JDBCUtils jdbc = new JDBCUtils();

jdbc.querySample("show tables;");
jdbc.querySample("select * from KeyTest;");

updateCnt = jdbc.excuteUpdate("drop table if exists test1;");
System.out.println("updateCnt is " + updateCnt + ".");

updateCnt = jdbc.excuteUpdate("drop table if exists test2;");
System.out.println("updateCnt is " + updateCnt + ".");

jdbc.close();
}
}