jdbc（java数据库连接）

最新推荐文章于 2024-07-18 16:09:33 发布

瑟瑟发抖的萌新学徒

最新推荐文章于 2024-07-18 16:09:33 发布

阅读量238

点赞数

文章标签：数据库 java sql

本文链接：https://blog.csdn.net/m0_56420338/article/details/129171334

版权

1、jdbc概述

全称：java datebse connectivity

作用：一种标准java应用编程接口（API），用来连接java编程语言和广泛的数据库。可以通过jdbc代码实现对数据库的操作。DML，DQL等进行数据库的增删改查

jdbc步骤：

1.加载驱动，通过反射机制

2.获得连接

3.执行sql语句处理结果

4.关闭资源

public class StudentDao {
//数据交互层（持久层）
//实现实体类和数据看库表映射，进行增删改查
//想student表中添加一条记录
	
//参数列表student对象
//返回类型boolean
	public boolean addStudent(Student stu) {
		Connection conn=null;
		Statement sm=null;
		try {           
		//			1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");//mysql 5.x版本适用
//			Class.forName("com.mysql.cj.jdbc.Driver");//mysql 8.x版本适用
		//			2.获得连接
			conn=DriverManager.getConnection(
//					"url",
//					"username",
//					"password"
					"jdbc:mysql://localhost:3306/"
					+ "day20?useUnicode=true&charcterEncoding=utf8",
					"root",
					"root"
					);// url资源路径 ，username 数据库名称 passuseUnicode=true&word密码
//			3.执行sql语句处理结果
			String sql="insert into student values("
					+stu.getStuId()+",'"
				    +stu.getStuName()+"',"+stu.getStuAge()+")";
//			获取语句对象
			sm=conn.createStatement();
//			1.DNL语句int executeUpdate（）返回受作用的行数
//			2.ResultSet DQL语句executeQuery（）返回包含结果的结果集
			int result =sm.executeUpdate(sql);
			if(result>0) {
				return true;
			}
			return false;
			
//			4.关闭资源
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	
		finally {
			
				try {
					if(sm!=null)
					sm.close();	
					if(conn!=null)conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		
		}

		
	}
//	删除
	public boolean deleteStudent(int stuId) {
		Connection conn=null;
		Statement sm=null;
		//		1.加载驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/"
							+ "day20?useUnicode=true&charcterEncoding=utf8",
							"root",
							"root"
					);
//			3.执行sql语句
			String sql="delete from student where stu_id="+stuId;
			sm=conn.createStatement();
			int result=sm.executeUpdate(sql);
			if(result>0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		finally{
			if(sm!=null) {
				try {
					sm.close();
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
		}
		
	}
//	改
	public boolean updateStudent(Student stu) {
		Connection conn=null;
		Statement sm=null;
		//		1.加载驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/"
							+ "day20?useUnicode=true&charcterEncoding=utf8",
							"root",
							"root"
					);
//			3.执行sql语句
			String sql="update student set stu_name='"+stu.getStuName()+"',stu_age="
					+ stu.getStuAge()+" where stu_id="+stu.getStuId();
	
			sm=conn.createStatement();
			int result=sm.executeUpdate(sql);
			if(result>0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		finally{
			if(sm!=null) {
				try {
					sm.close();
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
						}
		}
		
	}
	//查询——根据学生编号（某个条件）查询结果（单条数据）
//	参数stuId
//	返回类型Student对象
	public Student getStuById(int stuId) {
	Connection conn=null;
	Statement sm=null;
	ResultSet rs=null;
	try {           
		Class.forName("com.mysql.jdbc.Driver");
		conn=DriverManager.getConnection(				
				"jdbc:mysql://localhost:3306/"
				+ "day20?useUnicode=true&charcterEncoding=utf8",
				"root",
					"root"
				);
		sm=conn.createStatement();
		String sql="select*from student where stu_id="+stuId;
				//		ResultSet结果集
		rs=sm.executeQuery(sql);
//	rs.nex（）；判断是否含有下一条数据
//	rs.getxxx（列字段）根据字段获取对于的java类型数据
//		rs.getxxx（列索引）根据字段的顺序获取对应列的java类型数据		
		if(rs.next()) {
		String stuName=rs.getString("stu_name");//查询结果的字段顺序
		int stuAge=rs.getInt(3);
		Student stu=new Student(stuId, stuName, stuAge);
		return stu;
		}
		return null;
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		return null;
	}
	finally {
		try {
				if(rs!=null)rs.close();	
				if(sm!=null)sm.close();
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	
	}	
}
//	一览
//	无参
//	返回结果集合list，存取一致，允许重复
	public List<Student> showAllStu(){
		Connection conn=null;
		Statement sm=null;
		ResultSet rs=null;
		List<Student> list=new ArrayList<Student>();
	try {
		Class.forName("com.mysql.jdbc.Driver");
		conn=DriverManager.getConnection(				
				"jdbc:mysql://localhost:3306/"
				+ "day20?useUnicode=true&charcterEncoding=utf8",
				"root",
					"root"
				);
		sm=conn.createStatement();
		String sql="select*from student";
		rs=sm.executeQuery(sql);
		while(rs.next()) {
			int stuId=rs.getInt(1);
			String stuName=rs.getNString(2);
			int stuAge=rs.getInt(3);
			Student stu=new Student(stuId, stuName, stuAge);
		list.add(stu);
		}
		return list;
	}catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
		return null;
	} 
	finally {
		// TODO: handle finally clause
		
			try {
				if(rs!=null)
				rs.close();
				if(sm!=null)sm.close();
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
	}
	}
	
}

sql类型对应的java类型

sql	java
bit,bool	boolean
Integer	int
bigint	long
float,double	double
char,varchar	string
DECIMAL	BigDecimal
DATE	DATE

预处理语句对象：

PerparedStatement ps=conn.prepareStatement（）；

使用预处理语句对象时，sql语句要拼接参数用？代表替换占位符

ps.setxxx（index，value）；替换

语句对象的？，xxx表示值的java类型，index表示？的顺序，value替换的值。

练习：

package heros.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import hero.util.DBUtils;
import heros.vo.Heros;

public class HerosDao {

	public boolean addHeros(Heros her) {
		Connection conn=null;
//		Statement sm=null;
		PreparedStatement ps=null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/day20?useUnicode=true&charcterEncoding=utf8",
					"root",
					"root"
					);
//			sm=conn.createStatement();
//			String sql="insert into heros "
//					+ "values("+her.getId()+",'"+her.getName()+"', '"+
//					her.getPosition()+"',"+her.getAd()+")";
//					预处理语句对象是sql预计要拼接的参数用？代替
					
			String sql="insert into heros values(?,?,?,?)";
			ps=conn.prepareStatement(sql);
			ps.setInt(1,her.getId() );
			ps.setString(2, her.getName());
			ps.setString(3, her.getPosition());
			ps.setInt(4, her.getAd());
			
			int result=ps.executeUpdate();
			if(result>0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}finally {
			
				try {
					if(ps!=null)ps.close();
//					if(sm!=null)sm.close();
					if(conn!=null)conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			
		}
	}
	
	public boolean deleteHeros(String herName) {
		Connection conn=null;
//		Statement sm=null;
		PreparedStatement ps=null;
		try {
//			1。加载驱动，通过反射机制
//			2获得链接
//			3执行sql处理结果
//			4.关闭资源
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/day20?useUnicode=true&charcterEncoding=utf8",
					"root",
					"root"
					);
//			sm=conn.createStatement();
			String sql="delete from heros where heros_name=?";

			ps=conn.prepareStatement(sql);
			ps.setString(1, herName);
			int result=ps.executeUpdate();
			if(result>0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}finally {
			
				try {
					if(ps!=null)ps.close();
//					if(sm!=null)sm.close();
					if(conn!=null)conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			
		}
		
	}
	
public boolean updateHeros(Heros her) {

	PreparedStatement ps=null;
	Connection conn=DBUtils.getconn();
	String sql="update heros set heros_id=?,herso_position=?,herso_ad=? where heros_name=?";
	try {

		
		ps=conn.prepareStatement(sql);
		ps.setInt(1,her.getId() );
		ps.setString(2, her.getPosition());
		ps.setInt(3, her.getAd());
		ps.setString(4, her.getName());
		
		int result=ps.executeUpdate();
		if(result>0) {
			return true;
		}
		return false;
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		return false;
	}finally {
		DBUtils.close(conn, ps, null);
	}
	
}
	
	public Heros getHerById(String HerName) {
		
		Connection conn=DBUtils.getconn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select*from heros where heros_name=?";
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, HerName);
			rs=ps.executeQuery();
			if(rs.next()) {
				int herId=rs.getInt(1);
				String herPrsition=rs.getString(3);
				int herAd=rs.getInt(4);
				Heros her=new Heros(herId, HerName, herPrsition, herAd);
				return her;
			}
			return null;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBUtils.close(conn, ps, rs);	
		}	
	}
	
	public List<Heros> showAllHer(){
		Connection conn=DBUtils.getconn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select*from heros";
		List<Heros> list=new ArrayList<Heros>();
		try {
			
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery(sql);
			while(rs.next()) {
				int herId=rs.getInt(1);
				String herName=rs.getString(2);
				String herPrsition=rs.getString(3);
				int herAd=rs.getInt(4);
				Heros her=new Heros(herId, herName, herPrsition, herAd);
			list.add(her);
			}
			return list;
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		} 
		finally {
			// TODO: handle finally clause
			DBUtils.close(conn, ps, rs);
		}
	}
}


package hero.util;

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

public class DBUtils {
//	传入url，user，password获取指定的Connection
//	固定url，user，password获取指定的Connection
public static Connection getconn() {
	try {
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn=DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/day20?useUnicode=true&charcterEncoding=utf8",
				"root",
				"root"
				);
		return conn;
	} catch (Exception e) {
		// TODO: handle exception
		e.addSuppressed(e);
	return null;
	}
}
	//传入流对象关闭
	public static void close(Connection conn,PreparedStatement ps,ResultSet rs) {
		try {
			if(rs!=null)rs.close();
			if(ps!=null)ps.close();
			if(conn!=null)conn.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
}


package hero.view;

import java.util.Scanner;

import heros.dao.HerosDao;
import heros.vo.Heros;

public class HeroView {
	int choose=0;
	HerosDao dao=new HerosDao();
	public void view() {
		Scanner sc=new Scanner(System.in);
		do {
		System.out.println("=====开始=====");
		System.out.println("====1.添加=====");
		System.out.println("====2.删除=====");
		System.out.println("====3.修改=====");
		System.out.println("====4.查询=====");
		System.out.println("====5.一览=====");
		System.out.println("====6.退出=====");
		System.out.print("=======请输入：");
		 choose=sc.nextInt();
		switch(choose) {
		case 1:
			System.out.println("====1.添加=====");
			System.out.println("id");
			int heroId1=sc.nextInt();
			System.out.println("名称");
			String heroName1=sc.next();
			System.out.println("位置");
			String heroPosition1=sc.next();
			System.out.println("ad");
			int heroAd1=sc.nextInt();
			Heros hero1=new Heros(heroId1, heroName1, heroPosition1, heroAd1);
			if(dao.addHeros(hero1)) {
				System.out.println("成功");
			}else {
				System.out.println("失败");
			}
			break;
		case 2:
			System.out.println("要删除的名称");
			String heroName2=sc.next();
			if(dao.deleteHeros(heroName2)) {
				System.out.println("成功");
			}else {
				System.out.println("失败");
			}
			System.out.println("====2.删除=====");
			break;
		case 3:	
			System.out.println("====3.修改=====");
			System.out.println("输入名称");
			String heroName3=sc.next();
			System.out.println("要修改的id");
			int heroId3=sc.nextInt();
			System.out.println("要修改的位置");
			String heroPosition3=sc.next();
			System.out.println("要修改的ad");
			int heroAd3=sc.nextInt();
			Heros hero3=new Heros(heroId3, heroName3, heroPosition3, heroAd3);
			if(dao.updateHeros(hero3)) {
				System.out.println("成功");
			}else {
				System.out.println("失败");
			}
		
			break;
		case 4:
			System.out.println("====4.查询=====");
			System.out.println("输入名称");
			String heroName4=sc.next();
			if(dao.getHerById(heroName4)!=null) {
				System.out.println(dao.getHerById(heroName4));
				System.out.println("成功");
			}else {
				System.out.println("失败");
			}
			
			
			break;
		case 5:
			System.out.println("====5.一览=====");
			if(dao.showAllHer()!=null) {
				for (Heros her : dao.showAllHer()) {
					System.out.println(her);
				}
				System.out.println("成功");
			}else {
				System.out.println("失败");
			}
			break;
		case 6:
			System.out.println("====6.退出======");
		break;
		default: System.out.println("输入错误");
			
		}
	}while(choose!=6);
}
}

preparedStatement和statement的区别

1.preparedStatement可以在使用占位符“？”是预编译的，批处理比Statement效率高

2.preparedStatement对象的资源占用高，如果对数据看操作是一次性，用Statement

3.preparedStatement可以防止数据库注入。