Java调用Mysql

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即可使用该类