Java调用Mysql
连接步骤
添加mysql官方的java接口jar包,在后面详细介绍
连接Database
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/javabook", "USERNAME", "PASSWORD");
- 创建Database命令语句
Statement statement = connection.createStatement();
- 执行命令语句
statement.executeUpdate("create table Temp (col1 char(5), col2 char(5))");
- 获取请求query的结果集resultSet
ResultSet resultSet = statement.executeQuery
("select firstName, mi, lastName from Student where lastName " + " = 'Smith'");
resultSet的默认位置是null,以下代码打印所有数据信息
while (resultSet.next())
System.out.println(resultSet.getString(1) + " " + resultSet.getString(2) + " " + resultSet.getString(3));
也可以通过getString(“firstName”)获得firstName的值
完整程序代码如下:
public class DatabaseTest {
public static void main(String[] argv)
throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Driver loaded");
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost/Javadata", "user1", "12345"
);
System.out.println("connected");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(
"select * from Test order by age desc"
);
while(resultSet.next())
{
System.out.println(resultSet.getString("name") + " " + resultSet.getString("age"));
}
connection.close();
}
}
注意:
每一个单独语句会单独提交到数据库中,如果要分组进行提交的话,可以采用setAutoCommit(false)方法,并在一组statement语句的句末调用commit()方法提交整个组,而rollback()则撤销该组的提交。
rollback()可用于读取某个数据失败而导致整项不该修改的数据的修改。
使用带参数的SQL语句
不带参数的SQL语句:
String queryString = "select firstName, mi, " +
"lastName, title, grade from Student, Enrollment, Course " +
"where Student.ssn = '" + _ssn_ + "' and Enrollment.courseId "
+ "= '" + _courseId_ +
"' and Enrollment.courseId = Course.courseId " +
" and Enrollment.ssn = Student.ssn";
ResultSet rset = stmt.executeQuery(queryString)
调用connection.prepareStatement(),采用带参数的SQL语句(使用?作为参数):
preparedStatement = connection.prepareStatement(queryString);
String queryString = "select firstName, mi, " +
"lastName, title, grade from Student, Enrollment, Course " +
"where Student.ssn = ? and Enrollment.courseId = ? " +
"and Enrollment.courseId = Course.courseId";
preparedStatement.setString(1, ssn);
preparedStatement.setString(2, courseId);
ResultSet rset = preparedStatement.executeQuery();
IntelliJ中导入jar包
我使用的是Android Studio,但AS基于IJ开发,所以大同小异,步骤如下:
在Mysql官网下载mysql-connector-java-5.1.43.tar.gz
导入IntelliJ IDE中:
1. 点击 File -> Project Structure(快捷键 Ctrl + Alt + Shift + s),点击Project Structure界面左侧的modules。(在这里我的module是my_test)
2. 在 “Dependencies” 标签界面下,点击右边绿色的 “+”号,选择第一个选项“Jar dependency”,选择相应的jar包,点“OK”,jar包添加成功。
3. 假设包中有mysql.jdbc.Driver类,则在该module下,使用com.mysql.jdbc.Driver即可使用该类