JAVA基础知识点总结(十二)

12. 数据库【JDBC】

12.1 什么是JDBC？

JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC作用就是访问数据库。

12.2 JDBC访问数据库需要用到的类、接口和方法？

java.sql.DriverManager类[管理一组 JDBC 驱动程序的基本服务]

java.sql.ResultSet类[表示数据库结果集的数据表，通常通过执行查询数据库的语句生成。]

java.sql.Connection接口 [与特定数据库的连接（会话）。在连接上下文中执行 SQL 语句并返回结果。]

java.sql.Statement接口 [用于执行静态 SQL 语句并返回它所生成结果的对象]

java.sql.PreparedStatement接口[SQL 语句被预编译并存储在 PreparedStatement 对象中。然后可以使用此对象多次高效地执行该语句。]

使用JDBC连接数据库示例代码：

1.下载数据库驱动包“mysql-connector-java-5.1.38.jar”

2. 打开数据库创建数据表

create  table t_person(

per_id int  primary key auto_increment,

per_name varchar(20),

per_age int,

per_sex bit,

per_address  varchar(30)

);

3. 参照数据库表创建Java实体类

/**
 * 保存个人信息的java类
 * @author Administrator
 *
 */
@SuppressWarnings("serial")
public class Person implements Serializable{
	private  int perid;
	private  String pername;
	private int perage;
	private boolean persex;
	private  String peraddress;
	public int getPerid() {
		return perid;
	}
	public void setPerid(int perid) {
		this.perid = perid;
	}
	public String getPername() {
		return pername;
	}
	public void setPername(String pername) {
		this.pername = pername;
	}
	public int getPerage() {
		return perage;
	}
	public void setPerage(int perage) {
		this.perage = perage;
	}
	public boolean isPersex() {
		return persex;
	}
	public void setPersex(boolean persex) {
		this.persex = persex;
	}
	public String getPeraddress() {
		return peraddress;
	}
	public void setPeraddress(String peraddress) {
		this.peraddress = peraddress;
	}
}

4. 加载数据库驱动得到数据库连接

/**
 * 数据库连接的管理类
 * 1.加载数据库驱动
 * 2.得到数据库连接
 * @author Administrator
 *
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class  DBConnection{
	//定义数据库驱动名称
	private  static  final String DRIVERNAME="com.mysql.jdbc.Driver";
	//定义连接数据库的url
	private  static  final String URL="jdbc:mysql://127.0.0.1:3306/mydb1";
	//定义数据库访问用户名
	private static final String USERNAME="root";
	//定义访问数据库的密码
	private static final String PASSWORD="123456";
	
	//加载数据库驱动
	//1.构造方法加载数据库驱动
	/*
	public  DBConnection(){
		//通过反射机制加载数据库驱动
		try {
			Class.forName(DRIVERNAME);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	*/
	//2.通过静态代码块加载驱动
	static{
		//通过反射机制加载数据库驱动
				try {
					Class.forName(DRIVERNAME);
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				}
	   }
	/**
	 * 得到数据库连接
	 */
	public static Connection getMyConnection(){
		 //定义数据库连接对象
		Connection conn=null;
		//得到数据库连接
		try {
			conn=DriverManager.getConnection(URL,USERNAME,PASSWORD);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
}

5. 用Statement完成具体的操作

/**
 * 用Statement完成具体的增删改查操作
 * @author Administrator
 *
 */
public class StatementPerson {
    /**
     * 完成Person数据的添加
     * @param person
     * @return
     * @throws Exception
     */
	public  boolean insertPerson(Person person)throws Exception{
		   boolean  flag=false;
		   Connection  conn=null;
		   Statement  statement=null;
		   conn=DBConnection.getMyConnection();
		   statement= conn.createStatement();
		   String insertsql="insert into t_person values(null,'"+person.getPername()
		                             +"',"+person.getPerage()+","+person.isPersex()+",'"+person.getPeraddress()+"');";
		   System.out.println(insertsql);
		   if(statement.executeUpdate(insertsql) > 0) flag=true;
		   statement.close();
		   conn.close();
		   return flag;
	}
	
	/**
     * 完成Person数据的修改
     * @param person
     * @return
     * @throws Exception
     */
	public  boolean updatePerson(Person person)throws Exception{
		   boolean  flag=false;
		   Connection  conn=null;
		   Statement  statement=null;
		   conn=DBConnection.getMyConnection();
		   statement= conn.createStatement();
		   StringBuilder updatesql=new StringBuilder();
		   updatesql.append("update t_person set per_name='"+person.getPername()+"',");
		   updatesql.append("per_age="+person.getPerage()+",");
		   updatesql.append("per_sex="+person.isPersex()+",");
		   updatesql.append("per_address='"+person.getPeraddress()+"' ");
		   updatesql.append("where per_id="+person.getPerid()+";");
		   System.out.println(updatesql.toString());
		   if(statement.executeUpdate(updatesql.toString()) > 0) flag=true;
		   statement.close();
		   conn.close();
		   return flag;
	}
	/**
     * 完成Person数据的删除
     * @param person
     * @return
     * @throws Exception
     */
	public  boolean deletePerson(int perid)throws Exception{
		   boolean  flag=false;
		   Connection  conn=null;
		   Statement  statement=null;
		   conn=DBConnection.getMyConnection();
		   statement= conn.createStatement();
		  String  deletesql="delete from  t_person where per_id="+perid+";";
		   System.out.println(deletesql);
		   if(statement.executeUpdate(deletesql) > 0) flag=true;
		   statement.close();
		   conn.close();
		   return flag;
	}
	
	/**
     * 完成Person数据的查询所有
     * @param person
     * @return
     * @throws Exception
     */
	public  List<Person> selectPerson()throws Exception{
		  List<Person>  personList=null;
		   Connection  conn=null;
		   Statement  statement=null;
		   ResultSet  resultSet=null;
		   conn=DBConnection.getMyConnection();
		   statement= conn.createStatement();
		  String  selectsql="select * from t_person;";
		   System.out.println(selectsql);
		   resultSet=statement.executeQuery(selectsql);
		   personList=new ArrayList<Person>();
		   while(resultSet.next()){
			   //得到每一行中每一个列的具体数据值
			 int perid=resultSet.getInt("per_id");
			 String  pername=resultSet.getString("per_name");
			 int perage=resultSet.getInt("per_age");
			boolean persex= resultSet.getBoolean("per_sex");
			String peraddress=resultSet.getString("per_address");
			//将得到的每一列数据保存java对象
			Person  person=new Person();
			person.setPerid(perid);
			person.setPername(pername);
			person.setPerage(perage);
			person.setPersex(persex);
			person.setPeraddress(peraddress);
			//将保存有数据的java对象添加到集合中
			personList.add(person);
		   }
		   resultSet.close();
		   statement.close();
		   conn.close();
		   return personList;
	}
	
	/**
     * 完成Person数据根据id查询的操作
     * @param person
     * @return
     * @throws Exception
     */
	public  Person selectPersonById(int perid)throws Exception{
		   Person  person=null;
		   Connection  conn=null;
		   Statement  statement=null;
		   ResultSet  resultSet=null;
		   conn=DBConnection.getMyConnection();
		   statement= conn.createStatement();
		  String  selectsqlbyid="select * from t_person  where per_id="+perid+";";
		   System.out.println(selectsqlbyid);
		   resultSet=statement.executeQuery(selectsqlbyid);
		   if(resultSet.next()){
			   //得到一行中每一个列的具体数据值
			 int perid1=resultSet.getInt("per_id");
			 String  pername=resultSet.getString("per_name");
			 int perage=resultSet.getInt("per_age");
			boolean persex= resultSet.getBoolean("per_sex");
			String peraddress=resultSet.getString("per_address");
			//将得到的每一列数据保存java对象
			person=new Person();
			person.setPerid(perid1);
			person.setPername(pername);
			person.setPerage(perage);
			person.setPersex(persex);
			person.setPeraddress(peraddress);
		   }
		   resultSet.close();
		   statement.close();
		   conn.close();
		   return person;
	}
}

6. 用PreparedStatement完成具体的增删改查操作

/**
 * 8.用PreparedStatement完成具体的增删改查操作
 * @author Administrator
 *
 */
public class PreparedStatementPerson {
   /**
    * 添加Person信息
    * @param person
    * @return
    * @throws Exception
    */
	public  boolean  insertPerson(Person person)throws Exception{
		boolean flag=false;
		Connection  conn=null;
		PreparedStatement ps=null;
		conn=DBConnection.getMyConnection();
		String  insertsql="insert  into t_person values(null,?,?,?,?);";
		ps=conn.prepareStatement(insertsql);
		//为占位符“？”传递数据值
		ps.setString(1,person.getPername());
		ps.setInt(2,person.getPerage());
		ps.setBoolean(3,person.isPersex());
		ps.setString(4,person.getPeraddress());
		if(ps.executeUpdate() > 0) flag=true;
		ps.close();
		conn.close();
		return flag;
	}
	
	 /**
	    * 修改Person信息
	    * @param person
	    * @return
	    * @throws Exception
	    */
		public  boolean  updatePerson(Person person)throws Exception{
			boolean flag=false;
			Connection  conn=null;
			PreparedStatement ps=null;
			conn=DBConnection.getMyConnection();
			String  updatesql="update t_person set per_name=?,per_age=?,per_sex=?,per_address=? where per_id=?";
			ps=conn.prepareStatement(updatesql);
			//为占位符“？”传递数据值
			ps.setString(1,person.getPername());
			ps.setInt(2,person.getPerage());
			ps.setBoolean(3,person.isPersex());
			ps.setString(4,person.getPeraddress());
			ps.setInt(5,person.getPerid());
			if(ps.executeUpdate() > 0) flag=true;
			ps.close();
			conn.close();
			return flag;
		}
		
		 /**
		    * 删除Person信息
		    * @param person
		    * @return
		    * @throws Exception
		    */
			public  boolean  deletePerson(int perid)throws Exception{
				boolean flag=false;
				Connection  conn=null;
				PreparedStatement ps=null;
				conn=DBConnection.getMyConnection();
				String  deletesql="delete from t_person where per_id=?";
				ps=conn.prepareStatement(deletesql);
				//为占位符“？”传递数据值
				ps.setInt(1,perid);
				if(ps.executeUpdate() > 0) flag=true;
				ps.close();
				conn.close();
				return flag;
			}
			
			 /**
			    * 查询所有Person信息
			    * @param person
			    * @return
			    * @throws Exception
			    */
				public  List<Person>  selectPerson()throws Exception{
					 List<Person>  personlist=null;
					Connection  conn=null;
					PreparedStatement ps=null;
					ResultSet resultSet=null;
					conn=DBConnection.getMyConnection();
					String  deletesql="select * from t_person;";
					ps=conn.prepareStatement(deletesql);
					resultSet=ps.executeQuery();
					personlist=new ArrayList<Person>();
					while(resultSet.next()){
						Person  person=new Person();
						person.setPerid( resultSet.getInt("per_id")); 
						person.setPername(resultSet.getString("per_name"));
						person.setPerage(resultSet.getInt("per_age"));
						person.setPersex(resultSet.getBoolean("per_sex"));
						person.setPeraddress(resultSet.getString("per_address"));
						personlist.add(person);
					}
					resultSet.close();
					ps.close();
					conn.close();
					return personlist;
				}
				 /**
				    * 查询一个Person信息
				    * @param person
				    * @return
				    * @throws Exception
				    */
					public  Person  selectPersonById(int perid)throws Exception{
						 Person  person=null;
						Connection  conn=null;
						PreparedStatement ps=null;
						ResultSet resultSet=null;
						conn=DBConnection.getMyConnection();
						String  deletesql="select * from t_person where per_id=?;";
						ps=conn.prepareStatement(deletesql);
						ps.setInt(1,perid);
						resultSet=ps.executeQuery();
						if(resultSet.next()){
							person=new Person();
							person.setPerid( resultSet.getInt("per_id")); 
							person.setPername(resultSet.getString("per_name"));
							person.setPerage(resultSet.getInt("per_age"));
							person.setPersex(resultSet.getBoolean("per_sex"));
							person.setPeraddress(resultSet.getString("per_address"));
						}
						resultSet.close();
						ps.close();
						conn.close();
						return person;
					}
}

注：Statement和PreparedStatement的区别

Statement在执行sql语句的时候是通过拼接字符串的方式执行的，而PreparedStatement使用的是占位符的方式；PreparedStatement是预编译的，批处理比Statement效率高。

12.3 JDBC访问数据库的流程？