1、MySQL JDBC驱动下载
打开上面的下载地址,选择“Platform Independent”,然后下载tar.gz或zip格式的都行,之后解压出来得到Jdbc驱动(mysql-connector-java)的jar包,最后在项目中引用。
或者也可以Maven引用,Pom.xml中添加如下:
mysql
mysql-connector-java
5.1.35
2、创建示例数据库
1)创建数据库create database feedback;
use feedback;
2)创建数据库中用户CREATE USER sqluser IDENTIFIED BY 'sqluserpw';
grant usage on *.* to sqluser@localhost identified by 'sqluserpw';
grant all privileges on feedback.* to sqluser@localhost;
3)创建示例表和数据CREATE TABLE comments (
id INT NOT NULL AUTO_INCREMENT,
MYUSER VARCHAR(30) NOT NULL,
EMAIL VARCHAR(30),
WEBPAGE VARCHAR(100) NOT NULL,
DATUM DATE NOT NULL,
SUMMARY VARCHAR(40) NOT NULL,
COMMENTS VARCHAR(400) NOT NULL,
PRIMARY KEY (ID)
);
INSERT INTO comments values (default, 'lars', 'myemail@gmail.com','https://www.cjavapy.com/', '2009-09-14 10:33:11', 'Summary','My first comment' );
3、通过JDBC驱动连接Mysql的示例代码
创建以下类以连接到MySQL数据库并执行查询,插入和删除。打印查询结果的元数据(表名,列名)import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
public class MySQLAccess {
private Connection connect = null;
private Statement statement = null;
private PreparedStatement preparedStatement = null;
private ResultSet resultSet = null;
public void readDataBase() throws Exception {
try {
//将加载MySQL驱动程序,每个DB都有自己的驱动程序
Class.forName("com.mysql.jdbc.Driver");
//设置与数据库的连接
connect = DriverManager
.getConnection("jdbc:mysql://localhost/feedback?"
+ "user=sqluser&password=sqluserpw");
// Statements allow to issue SQL queries to the database
statement = connect.createStatement();
// Result set get the result of the SQL query
resultSet = statement
.executeQuery("select * from feedback.comments");
writeResultSet(resultSet);
// PreparedStatements can use variables and are more efficient
preparedStatement = connect
.prepareStatement("insert into feedback.comments values (default, ?, ?, ?, ? , ?, ?)");
// "myuser, webpage, datum, summary, COMMENTS from feedback.comments");
// Parameters start with 1
preparedStatement.setString(1, "Test");
preparedStatement.setString(2, "TestEmail");
preparedStatement.setString(3, "TestWebpage");
preparedStatement.setDate(4, new java.sql.Date(2009, 12, 11));
preparedStatement.setString(5, "TestSummary");
preparedStatement.setString(6, "TestComment");
preparedStatement.executeUpdate();
preparedStatement = connect
.prepareStatement("SELECT myuser, webpage, datum, summary, COMMENTS from feedback.comments");
resultSet = preparedStatement.executeQuery();
writeResultSet(resultSet);
//再次删除插入注释
preparedStatement = connect
.prepareStatement("delete from feedback.comments where myuser= ? ; ");
preparedStatement.setString(1, "Test");
preparedStatement.executeUpdate();
resultSet = statement
.executeQuery("select * from feedback.comments");
writeMetaData(resultSet);
} catch (Exception e) {
throw e;
} finally {
close();
}
}
private void writeMetaData(ResultSet resultSet) throws SQLException {
//现在从数据库中获取一些元数据
//结果集获取SQL查询的结果
System.out.println("The columns in the table are: ");
System.out.println("Table: " + resultSet.getMetaData().getTableName(1));
for (int i = 1; i<= resultSet.getMetaData().getColumnCount(); i++){
System.out.println("Column " +i + " "+ resultSet.getMetaData().getColumnName(i));
}
}
private void writeResultSet(ResultSet resultSet) throws SQLException {
// ResultSet is initially before the first data set
while (resultSet.next()) {
//可以通过名称获取列
//也可以通过列号获取列
//从1点开始
//例如resultSet.getSTring (2);
String user = resultSet.getString("myuser");
String website = resultSet.getString("webpage");
String summary = resultSet.getString("summary");
Date date = resultSet.getDate("datum");
String comment = resultSet.getString("comments");
System.out.println("User: " + user);
System.out.println("Website: " + website);
System.out.println("summary: " + summary);
System.out.println("Date: " + date);
System.out.println("Comment: " + comment);
}
}
// You need to close the resultSet
private void close() {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connect != null) {
connect.close();
}
} catch (Exception e) {
}
}
}
执行上面的示例代码public class Main {
public static void main(String[] args) throws Exception {
MySQLAccess dao = new MySQLAccess();
dao.readDataBase();
}
}