JDBC

JDBC操作步骤

1、添加jar包在项目的 lib 目录下

（1）mysql-connector-java-5.1.7-bin.jar

（2）添加完后需要鼠标右键，选择 Build Path 。之后会出现如下图的一个目录。

2、加载驱动

Class.forName("com.mysql.jdbc.Driver");

3、建立连接

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");

4、创建PreparedStatement对象

String sql = "select * from arttype";
PreparedStatement prepareStatement = connection.prepareStatement(sql);

5、执行sql语句

ResultSet resultSet = prepareStatement.executeQuery();

6、处理结果集

while(resultSet.next()) {
	String typeid = resultSet.getString("typeid");
	String typename = resultSet.getString("typename");
	String typedesc = resultSet.getString("typedesc");
	int typesort = resultSet.getInt("typesort");
	int typestate = resultSet.getInt("typestate");
	ArtType artType = new ArtType(typeid, typename, typedesc, typesort, typestate);
	System.out.println(artType);
}

7、关闭资源

if(connection != null) {
	connection.close();
}
if (prepareStatement != null) {
	prepareStatement.close();
}
if (resultSet != null) {
	resultSet.close();
}

JDBC整合

1、代码示例

package com.etime;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtil {
	public static Connection getConnection() throws Exception {
		String dbName = "mydb";
		String url = "jdbc:mysql://localhost:3306/" + dbName;
		String username = "root";
		String password = "root";
		//加载驱动
		Class.forName("com.mysql.jdbc.Driver");
		//建立连接
		Connection connection = DriverManager.getConnection(url, username, password);
		return connection;
	}
	//释放资源
	public static void release(Connection connection,Statement statement,ResultSet resultSet) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement .close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	//释放资源
	public static void release(Connection connection,Statement statement) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement .close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

2、测试类

（1）查询查询所有学生

package com.etime;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class Test {
	public static void main(String[] args) {
		/**
		 * 查询查询所有学生
		 */
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtil.getConnection();
			System.out.println(connection);
			statement = connection.createStatement();
			String sql = "select * from student";
			resultSet = statement.executeQuery(sql);
			while(resultSet.next()) {
				int studentId = resultSet.getInt(1);
				String studentName = resultSet.getString(2);
				Student student = new Student(studentId,studentName);
				System.out.println(student);
			}
		} catch (Exception e) {
			System.out.println(e.toString());
			e.printStackTrace();
		}finally {
			JDBCUtil.release(connection, statement, resultSet);
		}
	}
}

（2）查询指定学生

package com.etime;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test01 {
	public static void main(String[] args) {
		/**
		 * 查询指定学生
		 */
		Connection connection = null;
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtil.getConnection();
			//预编译
			String sql = "select * from student where studentname = ?";
			prepareStatement = connection.prepareStatement(sql);
			String name = "lili";
			prepareStatement.setString(1, name);
			resultSet = prepareStatement.executeQuery();
			while(resultSet.next()) {
				int studentId = resultSet.getInt("studentid");
				String studentName = resultSet.getString("studentname");
				Student student = new Student(studentId, studentName);
				System.out.println(student);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.release(connection, prepareStatement, resultSet);
		}
	}
}

（3）更新指定的学生

package com.etime;
/**
 * 更新指定的学生
 */
import java.sql.Connection;
import java.sql.PreparedStatement;

public class Test03 {
	public static void main(String[] args) {
		Connection connection = null;
		PreparedStatement prepareStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			//预编译
			String sql = "update student set studentname = ? where studentname like '%c%'";
			prepareStatement = connection.prepareStatement(sql);
			String name = "AA";
			prepareStatement.setString(1, name);
			int res = prepareStatement.executeUpdate();
			System.out.println(res);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.release(connection, prepareStatement);
		}
	}
}