JDBC详细例子

JDBC开始案例

一.准备工作

1.新创文件夹lib,复制JDBC.jar包,并且右键Build Path;

2.右键项目名Build Path-> Add Bilrary->JUnit->JUnit(作用:单元测试测试代码块);

二.写代码,用Dao模式

 使用PreparedStatement与使用Statement 编译SQL语句区别:

    Statement :先拼接SQL语句,如果变量里面带了数据库关键字,那么一并认为是关键字,不认为是普通的字符串.

    PreparedStatement:预先处理给定的SQL语句,对其执行语法检查,在SQL语句里面使用?占位符来替代后续要传递进来的变量值,将会被看成字符串,不会产生任何的关键字.(推荐)

package com.itheima.dao;

public interface UserDao {
	/**
	 * 查询所有
	 */
	void findALL();
	/**
	 * 添加记录
	 */
	void insert(int cid,String cname,String cdesc);
	/**
	 * 更新数据
	 */
	void upstate(int cid,String cname);
	/**
	 * 删除记录
	 */
	void delete(int cid);
}

package com.itheima.dao.impl;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import com.itheima.dao.UserDao;
import com.itheima.util.JDBCUtil;
public class UserDaoImpl implements UserDao{
	@Override
	public  void findALL() {
		//注册驱动 建立连接
		Connection conn = null;
		//创建PreparedStatement
		PreparedStatement pst = null;
		//得到Result
		ResultSet rs = null;
		try {
			conn = JDBCUtil.getConn();
			String sql = "select * from category"; //category是我数据库的表名,下同
			pst = conn.prepareStatement(sql);
			rs = pst.executeQuery();
			while(rs.next()) {
				int cid = rs.getInt("cid"); //cid表的列名,下同
				String cname = rs.getString("cname"); //cname表的列名,下同
				String cdesc = rs.getString("cdesc"); //cdesc表的列名,下同
				
				System.out.println(cid+"-"+cname+"-"+cdesc);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.release(conn, pst, rs);
		}
	}

	@Override
	public void insert(int cid, String cname, String cdesc) {
		//注册驱动 建立连接
		Connection conn =null;
		//创建PreparedStatement
		PreparedStatement pst = null;
		try {
			conn = JDBCUtil.getConn();
			String sql = "insert into category values(?,?,?)";
			pst = conn.prepareStatement(sql);
			pst.setInt(1, cid);
			pst.setString(2, cname);
			pst.setString(3, cdesc);
			int result = pst.executeUpdate();
			if(result>0) {
				System.out.println("插入成功");
			}else {
				System.out.println("插入失败");
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.release(conn, pst);
		}
		
	}

	@Override
	public void upstate(int cid, String cname) {
		//注册驱动 建立连接
		Connection conn = null;
		//创建PreparedStatement
		PreparedStatement ps = null;
		try {
			conn = JDBCUtil.getConn();
			String sql = "update category set cid=? where cname=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, cid);
			ps.setString(2, cname);
			int result = ps.executeUpdate();
			if(result>0) {
				System.out.println("更新成功");
			}else {
				System.out.println("更新失败");
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.release(conn, ps);
		}
	}

	@Override
	public void delete(int cid) {
		//注册驱动 建立连接
		Connection conn =null;
		//创建PreparedStatement
		PreparedStatement ps = null;
		try {
			conn = JDBCUtil.getConn();
			String sql = "delete from category where cid=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, cid);
			int result = ps.executeUpdate();
			if(result>0) {
				System.out.println("删除成功");
			}else {
				System.out.println("删除失败");
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.release(conn, ps);
		}
	}
}

package com.itheima.test;
import org.junit.Test;
import com.itheima.dao.UserDao;
import com.itheima.dao.impl.UserDaoImpl;

public class TestUserDaoImpl {
	
	@Test
	public void testFindAll() {
		UserDao u1 = new UserDaoImpl();
		u1.findALL();
	}
	
	@Test
	public void  testInsert() {
		UserDao u1 = new UserDaoImpl();
		u1.insert(6, "手环戒指", "金戒指,银项链");
	}
	
	@Test
	public void testUpadate() {
		UserDao u1 = new UserDaoImpl();
		u1.upstate(6, "电脑办公");
	}
	
	@Test
	public void testUpdate(){
		UserDao u1 = new UserDaoImpl();
		u1.delete(6);
	}
}

package com.itheima.util;
import java.io.IOException;
import java.io.InputStream;
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 {
	static String url = null;
	static String user = null;
	static String password=null;
	
	static {
		InputStream inStream = null;
		//注册驱动 建立连接
		try {
			Properties properties = new Properties();
                        //文件在src里
			inStream = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
			//导入输入流
			properties.load(inStream);
			//读取属性
			url = properties.getProperty("url");
			user = properties.getProperty("user");
			password = properties.getProperty("password");
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(null!=inStream) {
					inStream.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}finally {
				inStream = null;
			}
		}
	}
	
	public static Connection getConn() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url, user, password);
			return conn;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 释放资源
	 * @param conn
	 * @param st
	 * @param rs
	 */
	public static void release(Connection conn,Statement st,ResultSet rs) {
		closeConn(conn);
		closeSt(st);
		closeRs(rs);
	}	
	public static void release(Connection conn,Statement st) {
		closeConn(conn);
		closeSt(st);
	}
	private static void closeConn(Connection conn){
		try {
			if(null!=conn) {
				conn.close();
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			conn = null;
		}
	}
	private static void closeSt(Statement st){
		try {
			if(null!=st) {
				st.close();
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			st = null;
		}
	}
	private static void closeRs(ResultSet rs){
		try {
			if(null!=rs) {
				rs.close();
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			rs = null;
		}
	}
}

//jdbj.properties文件内容
url=jdbc:mysql://localhost/day06?serverTimezone=UTC  //day06是数据库名
user=root  //root数据库登录名
password=123456  //123456登录密码

三.好好细品