JAVA JDBC

1.java连接数据库

1.1加载驱动类
导入jar文件，jar都是class
右击项目名----->properties---->JavaBuildPath---->Libraries---->add external jar
常用类如下：
java.sql.Connection;
java.sql.DriverManager;
java.sql.Statement;
例子如下
package lesson1;

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

public class JdbcDemo1 {

public static void main(String[] args) {
	try {
		//1.加载驱动类
		Class.forName("oracle.jdbc.driver.OracleDriver");
		//2.获得连接 java.sql.Connection
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@PC1:1521:ORACLE","scott","tiger");
		//System.out.println(con);
		//3.执行sql语句   java.sql.Statement
		Statement stmt = con.createStatement();
		//4.执行查询语句--->查询得到的是结果集java.sql.ResultSet
		String sql = "select ename from emp";
		ResultSet rs =stmt.executeQuery(sql);
		//5.访问结果集
		while(rs.next()){
			//System.out.println(rs.getString(1));
			//关闭资源---->异常后面要细致处理
			System.out.println(rs.getString("ename"));
		}
		rs.close();
		stmt.close();
		con.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
}

运行结果如下

SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

把数据库的连接和资源的关闭操作进行包装JdbcUtil
因为每个人的地址都不一样，建立了一个properties文件，将信息放在该文件中
将来不同的人使用只要更改properties文件即可
properties如下

driverClass=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@PC1:1521:ORACLE
user=scott
pass=tiger

代码如下

package lesson1;

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

public class JdbcUtil {
	private static Properties prop = new Properties();
	static{
		try {
			prop.load(JdbcUtil.class.getResourceAsStream("/database.properties"));
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
	public static Connection getConn(){
		try {
			Class.forName(prop.getProperty("driverClass"));
			Connection con = DriverManager.getConnection(
					prop.getProperty("url"),prop.getProperty("user")
					,prop.getProperty("pass"));
			return con;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	public static void close(Connection con ,Statement stmt,ResultSet rs){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
			if(stmt!=null){
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(con!=null){
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		
	}
}

2.把针对表的操作进行包装DAO

一张表----类 对应（表—>类 列---->属性 记录----->对象）
就是将java对数据库的操作封装在DAO中，代码如下

package lesson1;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
//增加一条记录
	public int addUser(User user) {
		Connection con = null;
		Statement stmt = null;
		int n = 0;
		try {
			con = JdbcUtil.getConn();
			stmt = con.createStatement();
			String sql = "insert into bbs_user(id,username,userpass,email)" + " values(" + user.getId() + ",'"
					+ user.getUsername() + "','" + user.getUserpass() + "','" + user.getEmail() + "')";
			n = stmt.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.close(con, stmt, null);
		}
		return n;
	}
	//查询所有
	public List<User> getAll(){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<User> users = new ArrayList<User>();
		try {
			con = JdbcUtil.getConn();
			stmt = con.createStatement();
			String sql = "select* from bbs_user";
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setUserpass(rs.getString("userpass"));
				user.setEmail(rs.getString("email"));
				users.add(user);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.close(con, stmt, rs);
		}
		return users;
	}
}

测试代码

package lesson1;

import java.util.List;

public class JdbcDemo4 {

	public static void main(String[] args) {
		//测试增加一条记录
		UserDao ud = new UserDao();
		/*User user = new User(1001,"tianqi","1234tian","t@163.com");
		int n = ud.addUser(user);
		System.out.println(n);*/
		//测试查询所有
		List<User>users = ud.getAll();
		for(User user:users){
			System.out.println(user);
		}
	}

}

结果如下
User [id=1001, username=tianqi, userpass=1234tian, email=t@163.com]
User [id=1002, username=zhangsan, userpass=zzzs123, email=z2@163.com]
User [id=1003, username=lisi, userpass=237162, email=z1@163.com]
User [id=1004, username=zhangsan, userpass=zsan, email=zs@163.com]

3.java.sql.Statement------>java.sql.PreparedStatement
java.sql.PreparedStatement
sql语句中需要传递的值可以使用？占位符号表示，提高效率。这样可以起到预编译的效果，性能较好，防止sql注入；