jdbc连接数据库示例

java.sql.Connection完成对某一指定数据库的联接
java.sql.Statement在一个给定的连接中作为SQL执行声明的容器，他包含了两个重要的子类型。
Java.sql.PreparedSatement 用于执行预编译的sql声明
Java.sql.CallableStatement用于执行数据库中存储过程的调用
java.sql.ResultSet对于给定声明取得结果的途径

示例代码——连接mysql：

package cn.itcast.cd.jsp.domain;

import java.util.Date;

public class Student {
	private int id;
	private String name;
	private String sex;
	private int age;
	private Date birthday;
	public int getId() {
		return id;
	}
	public void setId(int 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 int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", sex=" + sex
				+ ", age=" + age + ", birthday=" + birthday + "]";
	}
}

package cn.itcast.cd.utils;

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

/**
 * 操作JDBC的工具类
 * @author dendy
 * @version 1.0
 */
public class DbUtils {
	private static String url = "jdbc:mysql://localhost/jdbc";
	private static String user = "root";
	private static String password = "admin";
			
	private DbUtils(){  //不允许工具类拥有实例对象
		
	}
	
	//只需要一份驱动，所以放到静态代码块中
	static {
		try {
                        //利用Class.forName调用com.mysql.jdbc.Driver的静态代码块，得到一个Driver对象的实例，而不需要手动创建.
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获得数据库连接
	 * @return 返回Jave.sql.Connection实例，发生异常返回null.
	 */
	public static Connection getConnection(){
		try {
			Connection connection = DriverManager.getConnection(url, user, password);
			return connection;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 关闭Connection、Statement、ResultSet对象.
	 * @param connection
	 * @param statement
	 * @param resultSet
	 */
	public static void close(Connection connection, Statement statement, ResultSet resultSet){
		close(connection);
		close(statement);
		close(resultSet);
	}
	
	/**
	 * 关闭Connection、Statement对象.
	 * @param connection
	 * @param statement
	 */
	public static void close(Connection connection, Statement statement){
		close(connection);
		close(statement);
	}
	
	/**
	 * 关闭Connection对象
	 * @param connection
	 */
	public static void close(Connection connection){
		try {
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 关闭Statement对象
	 * @param statement
	 */
	public static void close(Statement statement){
		try {
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 关闭ResultSet对象
	 * @param resultSet
	 */
	public static void close(ResultSet resultSet){
		try {
			resultSet.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

package cn.itcast.dao.Impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.itcast.cd.jsp.dao.IStudentDao;
import cn.itcast.cd.jsp.domain.Student;
import cn.itcast.cd.utils.DbUtils;

public class StudentDao implements IStudentDao {

	@Override
	public void add(Student stu) {
		// 连接数据库
		Connection connection = DbUtils.getConnection();

		PreparedStatement preparedStatement = null;
		String sql = "INSERT INTO student VALUES (null,?,?,?,?)";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, stu.getName());
			preparedStatement.setString(2, stu.getSex());
			preparedStatement.setInt(3, stu.getAge());

			// 第二参数为java.sql.Date类型，为java.util.Date的子类，需要处理
			preparedStatement.setDate(4, new Date(stu.getBirthday().getTime()));

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 关闭资源
			DbUtils.close(connection, preparedStatement);
		}
	}

	@Override
	public void delete(int id) {
		// 连接数据库
		Connection connection = DbUtils.getConnection();

		PreparedStatement preparedStatement = null;
		String sql = "DELETE FROM student WHERE id = " + id;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 关闭资源
			DbUtils.close(connection, preparedStatement);
		}
	}

	@Override
	public void update(int id, Student stu) {
		// 连接数据库
		Connection connection = DbUtils.getConnection();

		PreparedStatement preparedStatement = null;
		String sql = "UPDATE student SET name=?,sex=?,age=?,birthday=? WHERE ID="
				+ id;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, stu.getName());
			preparedStatement.setString(2, stu.getSex());
			preparedStatement.setInt(3, stu.getAge());
			preparedStatement.setDate(4, new Date(stu.getBirthday().getTime()));

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 关闭资源
			DbUtils.close(connection, preparedStatement);
		}
	}

	@Override
	public Student get(int id) {
		// 连接数据库
		Connection connection = DbUtils.getConnection();
		ResultSet resultSet = null;
		PreparedStatement preparedStatement = null;
		String sql = "SELECT * FROM student WHERE ID=" + id;
		try {
			preparedStatement = connection.prepareStatement(sql);

			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				Student stu = new Student();
				stu.setName(resultSet.getString("name"));
				stu.setSex(resultSet.getString("sex"));
				stu.setAge(resultSet.getInt("age"));
				stu.setBirthday(resultSet.getDate("birthday"));
				stu.setId(resultSet.getInt("id"));

				return stu;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 关闭资源
			DbUtils.close(connection, preparedStatement, resultSet);
		}
		return null;
	}

	@Override
	public List<Student> getAll() {
		List<Student> stuList = new ArrayList<Student>();
		// 连接数据库
		Connection connection = DbUtils.getConnection();
		ResultSet resultSet = null;
		
		PreparedStatement preparedStatement = null;
		String sql = "SELECT * FROM student";
		try {
			preparedStatement = connection.prepareStatement(sql);

			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Student stu = new Student();
				stu.setName(resultSet.getString("name"));
				stu.setSex(resultSet.getString("sex"));
				stu.setAge(resultSet.getInt("age"));
				stu.setBirthday(resultSet.getDate("birthday"));
				stu.setId(resultSet.getInt("id"));

				stuList.add(stu);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 关闭资源
			DbUtils.close(connection, preparedStatement, resultSet);
		}
		return stuList;
	}

	@Override
	public List<Student> get(String name) {
		List<Student> stuList = new ArrayList<Student>();
		// 连接数据库
		Connection connection = DbUtils.getConnection();
		ResultSet resultSet = null;
		
		PreparedStatement preparedStatement = null;
		String sql = "SELECT * FROM student WHERE name='"+name+"'";
		try {
			preparedStatement = connection.prepareStatement(sql);

			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Student stu = new Student();
				stu.setName(resultSet.getString("name"));
				stu.setSex(resultSet.getString("sex"));
				stu.setAge(resultSet.getInt("age"));
				stu.setBirthday(resultSet.getDate("birthday"));
				stu.setId(resultSet.getInt("id"));

				stuList.add(stu);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 关闭资源
			DbUtils.close(connection, preparedStatement, resultSet);
		}
		return stuList;
	}

}

转载于:https://my.oschina.net/dendy/blog/398163