JDBC获取与MYSQL的链接并且增删改查表内容

把驱动链接用户名和密码写入properties 文件中 我起名为MYSQL关联的数据库名 qiye.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/qiye
name=root
password=123

ConfigManager 读取properties 文件的信息并且加载

package jdbc.CM;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;



public class ConfigManager {
	  private Properties properties;
	  private static ConfigManager configManager;
	 
	  private ConfigManager() {
		  //读取文件ebook
		  InputStream in = ConfigManager.class.getClassLoader().getResourceAsStream("qiye.properties");
	     //加载ebook
		  properties = new Properties();
	      try {
			properties.load(in);
			in.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	  }
	  //单例模式 懒汉模式 需要才new对象 如果有了就不用NEW
	public static ConfigManager instance() {
		if(configManager==null) {
			configManager=new ConfigManager();
		}
		return configManager;	
	}
	public String getKey(String key) {
		return properties.getProperty(key);	
	}
}

getConnection 获取驱动链接 用户名密码 链接数据库MYSQL

prepareStatement可以防止SQL注入

package jdbc.Dao;

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

import jdbc.CM.ConfigManager;

public class qiyeDao {

	Connection con=null;
	PreparedStatement pst=null;
	ResultSet rs=null;
	ConfigManager configManager = ConfigManager.instance();
//得到链接的方法
	public  void getConnection() {
	String driver=	configManager.getKey("driver");
	String url=configManager.getKey("url");
	String name=configManager.getKey("name");
	String password=configManager.getKey("password");
		try {
			Class.forName(driver);
			con=DriverManager.getConnection(url, name, password);
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//重写查询的方法
	public ResultSet executeQuery(String sql, Object... args) {
//先获得链接
		this.getConnection();
		try {
			pst = con.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				pst.setObject(i + 1, args[i]);
			}
			return pst.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	
	}
//增加，删除和修改用的一个方法
	public  int executeUpDate(String sql,Object...args) {
		this.getConnection();
		try {
			pst=con.prepareStatement(sql);
			for(int i=0;i<args.length;i++) {
				pst.setObject(i+1, args[i]);
			}
			 return pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
			
		}finally {
			this.closeAll();
		}
		
	}
	//关闭
	public void closeAll() {
		if(rs!=null)
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		if(pst!=null)
			try {
				pst.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();
			}
	}
}

写一个数据库下   表的实体类

package jdbc.Entity;

public class project {
	    private long id;
		private String projectName;
		public long getId() {
			return id;
		}
		public void setId(long id) {
			this.id = id;
		}
		public String getProjectName() {
			return projectName;
		}
		public void setProjectName(String projectName) {
			this.projectName = projectName;
		}
		@Override
		public String toString() {
			return "project [id=" + id + ", projectName=" + projectName + "]";
		}
}

写一个表的接口interface 定义方法

package jdbc.Dao;

import java.util.List;


import jdbc.Entity.project;

public interface projectInterfaceDao {
	List<project> getAll(long id); //得到所有的内容
	int Add(project pro);//添加信息
	int upDate(project pro);//修改信息
	int del(long id);	//删除信息
}

写一个继承类来重写接口方法 

package jdbc.Dao.Impl;

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

import jdbc.Dao.projectInterfaceDao;
import jdbc.Dao.qiyeDao;
import jdbc.Entity.project;

public class projectDaoImpl extends qiyeDao implements projectInterfaceDao {
//继承yiqe的底层获取数据库链接和基础的executeQuery executeUpdate 继承接口interface 重写方法
	@Override
	public List<project> getAll(long id) {//通过id 获取信息 获取全部不用传参
		List<project>  list= new ArrayList<project>();
		String sql = "select*from project where id=?";
		ResultSet rs = super.executeQuery(sql,id);
		try {
			while(rs.next()) {
				project p = new project();
				p.setId(rs.getLong("id"));
				p.setProjectName(rs.getString("projectName"));
				list.add(p);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			super.closeAll();
		}
		return list;
	}

	
	@Override
	public int Add(project pro) {
		String sql = "insert into project values(null,?)";
		int count =super.executeUpDate(sql, pro.getProjectName());
        return count;
	}

	@Override
	public int upDate(project pro) {
		String sql = "update  project set projectname=? where id=?";
		return super.executeUpDate(sql, pro.getProjectName(), pro.getId());

	}

	@Override
	public int del(long id) {
		String sql = "delete from project where id=?";
		return super.executeUpDate(sql, id);
	}

}

用junit测试增删改查     

package jdbc.Test;

import static org.junit.jupiter.api.Assertions.*;

import java.util.List;

import org.junit.jupiter.api.Test;

import jdbc.Dao.projectInterfaceDao;
import jdbc.Dao.Impl.projectDaoImpl;
import jdbc.Entity.project;

class projectTest {
	
projectInterfaceDao pid=new projectDaoImpl();
	@Test
	void testGetAll() {
		List<project> list=pid.getAll(1);
		System.out.println(list);
		
	}
	@Test
	void testAdd() {
		project p=new project();
		p.setProjectName("系统开发部");
	int	count=pid.Add(p);
	if(count>0) {
		System.out.println("添加成功");
	}else {
		System.out.println("添加失败");
	}
		
	}
		
	@Test
	void testupdate() {
		project p=new project();
		p.setProjectName("系");
		p.setId(5);;
	int	count=pid.upDate(p);
	if(count>0) {
		System.out.println("修改成功");
	}else {
		System.out.println("修改失败");
	}
		
	}
	@Test
	void testdel() {
	
	int	count=pid.del(5);
	if(count>0) {
		System.out.println("删除成功");
	}else {
		System.out.println("删除失败");
	}
		
	}
}