MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性,MySQL所使用的SQL语言是用于访问数据库的最常用标准化语言。
1、Java 连接 MySQL 需要驱动包,Mysql服务器目前最新版本为:mysql-5.6.22-winx64.zip
2、JDBC驱动目前最新版本为:mysql-connector-java-3.1.14
在数据库中创建测试数据,执行插入sql语句如下:CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL DEFAULT '' COMMENT '姓名',
`age` varchar(255) NOT NULL DEFAULT '',
`sex` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
定义实体类,为了便于操作和接口定义,是非必须的:public class Student {
private String id;
private String name;
private String sex;
private String age;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAge() {
return age;
}
public void setage(String age) {
this.age = age;
}
}
连接mysql数据库工具类,代码如下:private static Connection getConn() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/dbname";// 连接Url地址
String username = "root";
String password = "123456";
Connection conn = null;
try {
Class.forName(driver); //classLoader,加载mysql驱动
conn = (Connection) DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
请将localhost改成你实际应用中的Mysql服务器地址,dbname改成你实际的数据库名。
insert(插入)private static int insert(Student student) {
Connection conn = getConn();
int i = 0;
String sql = "insert into students (name,sex,age) values(?,?,?)";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(1, student.getName());
pstmt.setString(2, student.getSex());
pstmt.setString(3, student.getAge());
i = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
update(修改)private static int update(Student student) {
Connection conn = getConn();
int i = 0;
String sql = "update students set age='" + student.getAge() + "' where name='" + student.getName() + "'";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
i = pstmt.executeUpdate();
System.out.println("resutl: " + i);
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
select(查询)private static Integer selectAll() {
Connection conn = getConn();
String sql = "select * from students";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement)conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
int col = rs.getMetaData().getColumnCount();
while (rs.next()) {
for (int i = 1; i <= col; i++) {
System.out.print(rs.getString(i) + "\t");
if ((i == 2) && (rs.getString(i).length()
System.out.print("\t");
}
}
System.out.println("");
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
delete(删除)private static int delete(String name) {
Connection conn = getConn();
int i = 0;
String sql = "delete from students where name='" + name + "'";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
i = pstmt.executeUpdate();
System.out.println("resutl: " + i);
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}