java JDBC crud无参

1.配置文件

jdbc.name = root
jdbc.password = 2510.62
jdbc.url = jdbc:mysql://localhost/demo?useSSL=true&characterEncoding=utf8
jdbc.driver = com.mysql.jdbc.Driver

2.连接sql的工具类

package java_n20.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
 * ClassName: DBHelper 
 * @Description: 获得数据库连接的工具类
 * @author cai
 * @date 2018年10月22日 
 */
public class DBHelper {
	
		//1.用户名
	private static String name;
		//2.密码
	private static String password;
		//3.地址
	private static String url;
		//4.驱动，从外部引进mysql-connector-jar文件
	private static String driver;
	
	static {
		Properties prop = new Properties();
		try {
			//1.读取jdbc.properties文件的内容
			prop.load(new FileInputStream(new File("src/jdbc.properties")));
			name = prop.getProperty("jdbc.name");
			url = prop.getProperty("jdbc.url");
			password = prop.getProperty("jdbc.password");
			driver = prop.getProperty("jdbc.driver");
			//2.加载驱动
			Class.forName(driver);
		} catch (IOException e) {
					e.printStackTrace();
		} catch (ClassNotFoundException e) {
					e.printStackTrace();
		}			
	}
	public static Connection getDBConnection() {  //工具类方法一般为静态，而且是不能实例化的
		Connection conn= null;
		try {
			//3.获得连接
			conn= DriverManager.getConnection(url, name, password);
		} catch (SQLException e) { 	
			e.printStackTrace();
		}
		return conn;
	}	
	private DBHelper() {
		
	}	
}

 

3.接口

package java_n20.dao;

public interface EmpDao {

	public void addEmp() throws Exception;
	public void deleteEmp() throws Exception;
	public void updateEmp() throws Exception;
	public void selectOneEmp() throws Exception;
	public void selectAll() throws Exception;
	public void selectByPage() throws Exception;
	public void selectCount() throws Exception;
}

 

4.接口实现类

package java_n20.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java_n20.dao.EmpDao;
import java_n20.util.DBHelper;

public class EmpDaoImpl implements EmpDao{

	private Connection conn = null;
	private PreparedStatement ps = null;
	ResultSet rs =null;
	/**
	 * 添加员工  insert
	 */
	@Override
	public void addEmp() throws Exception {

		//1.获得数据库连接
		conn = DBHelper.getDBConnection();
		//2.准备sql语句
		String sql = "insert into student (id,name) values (999,'刘尼古拉斯')";
		//3.(预)编译sql  Statement-->Preparement(预编译对象，存放的是编译后的sql语句)
		//Preparement 用于将参数化的sql语句发送到数据库
		//PreparedStatement表示预编译的sql语句的对象，sql语句已预编译并存储在这个对象中
		ps = conn.prepareStatement(sql);
		//4.执行sql添加
		int result = ps.executeUpdate();  //返回值表示更改的记录数
		System.out.println(result);
		//5.关闭连接，释放资源
		conn.close();
	}

	@Override
	public void deleteEmp() throws Exception {
		conn = DBHelper.getDBConnection();
		String sql = "delete from student where id = 999";
		ps = conn.prepareStatement(sql);
		//4.执行sql删除
		int result = ps.executeUpdate();  
		System.out.println(result);
		conn.close();
	}
	@Override
	public void updateEmp() throws Exception {
		conn = DBHelper.getDBConnection();
		String sql = "update student set name='张老大' where id = 901";
		ps = conn.prepareStatement(sql);
		//4.执行sql更改
		int result = ps.executeUpdate();  
		System.out.println(result);
		conn.close();
	}
	@Override
	public void selectOneEmp() throws Exception {
		conn = DBHelper.getDBConnection();
		String sql = "select * from student where id = 901";
		ps = conn.prepareStatement(sql);
		//4.执行sql查询
		rs = ps.executeQuery();  //返回一个结果集，是数据库里面查询的数据信息
		
		//4.1获取返回的数据信息
		if(rs.next()) {  //指针下一行有没有数据
			String name = rs.getString("name"); //参数为列信息
			String sex = rs.getString("sex");
			System.out.println(name+sex);
		}
		//5.关闭
		rs.close();
		ps.close();
		conn.close();
	}
	@Override
	public void selectAll() throws Exception {
		conn = DBHelper.getDBConnection();
		String sql = "select * from student";
		ps = conn.prepareStatement(sql);
		//4.执行sql查询
		rs = ps.executeQuery();  //返回一个结果集，是数据库里面查询的数据信息
		
		//4.1获取返回的数据信息
		while(rs.next()) {  //指针下一行有没有数据
			String name = rs.getString("name"); //参数为列信息
			String sex = rs.getString("sex");
			System.out.println(name+sex);
		}
		//5.关闭
		rs.close();
		ps.close();
		conn.close();
	}

	@Override
	public void selectByPage() throws Exception {
		conn = DBHelper.getDBConnection();
		String sql = "select * from student limit 0,5";
		ps = conn.prepareStatement(sql);
		//4.执行sql查询
		rs = ps.executeQuery();  //返回一个结果集，是数据库里面查询的数据信息
		
		//4.1获取返回的数据信息
		while(rs.next()) {  //指针下一行有没有数据
			String name = rs.getString("name"); //参数为列信息
			String sex = rs.getString("sex");
			System.out.println(name+sex);
		}
		//5.关闭
		rs.close();
		ps.close();
		conn.close();
		
	}

	@Override
	public void selectCount() throws Exception {
		conn = DBHelper.getDBConnection();
		String sql = "select count(*) from student";
		ps = conn.prepareStatement(sql);
		//4.执行sql查询
		rs = ps.executeQuery();  //返回一个结果集，是数据库里面查询的数据信息
		
		//4.1获取返回的数据信息
		while(rs.next()) {  //指针下一行有没有数据
			int total = rs.getInt("count(*)");
			System.out.println(total);
		}
		//5.关闭
		rs.close();
		ps.close();
		conn.close();
		
	}
}

 

5.测试类

package java_n20.test;

import java_n20.dao.EmpDao;
import java_n20.dao.impl.EmpDaoImpl;

public class TestEmp {

	public static void main(String[] args) {
		EmpDao empDao = new EmpDaoImpl();
		try {
			empDao.selectCount();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}