JDBC链接SQLServer数据库-优快云博客

本文链接：https://blog.youkuaiyun.com/gplihf/article/details/52397622

本文详细介绍了如何在 Dao 层通过 JDBC API 实现与 SQLServer 数据库的连接，包括驱动配置、连接建立、SQL 执行及结果处理等关键步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

工具类

package com.lihf.db;

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

public class DBUtil {

	private static final String URL ="jdbc:sqlserver://localhost:1433;" +
			"databaseName=test";
	private static final String NAME="ehs";
	private static final String PASSWORD="ehs";
	
	private static Connection conn = null;
	
	static{
		//1.加载驱动程序
		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			//2.获取数据库的链接
			conn = DriverManager.getConnection(URL, NAME, PASSWORD);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection(){
		return conn;
	}
	
	
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		//1.加载驱动程序
		Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
		//2.获取数据库的链接
		Connection conn = DriverManager.getConnection(URL, NAME, PASSWORD);
		//3.操作数据库
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("select user_name,age from imooc_goddess");
		while(rs.next()){
			System.out.println(rs.getString("user_name"));
			System.out.println(rs.getInt("age"));
		}
		
	}
}

Dao层

package com.lihf.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
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 java.util.Map;

import com.lihf.db.DBUtil;
import com.lihf.model.Goddess;

public class GoddessDao {
	public void addGoddess(Goddess g) throws SQLException{
		Connection conn = DBUtil.getConnection();
		String sql="" +
				"insert into imooc_goddess" +
				"(user_name,sex,age,birthday,email,mobile," +
				"create_user,create_date,update_user,update_date,isdel)" +
				"values(" +
				"?,?,?,?,?,?,?,getdate(),?,getdate(),?)";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, g.getUser_name());
		ptmt.setInt(2, g.getSex());
		ptmt.setInt(3, g.getAge());
		ptmt.setDate(4, new Date(g.getBirthday().getTime()));
		ptmt.setString(5, g.getEmail());
		ptmt.setString(6, g.getMobile());
		ptmt.setString(7, g.getCreate_user());
		ptmt.setString(8, g.getUpdate_user());
		ptmt.setInt(9, g.getIsdel());
		ptmt.execute();
	}
	
	public void updateGoddess(Goddess g) throws SQLException{
		Connection conn = DBUtil.getConnection();
		String sql="" +
				" update imooc_goddess " +
				" set user_name=?,sex=?,age=?,birthday=?,email=?,mobile=?, " +
				" update_user=?,update_date=getdate(),isdel=? " +
				" where id=? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, g.getUser_name());
		ptmt.setInt(2, g.getSex());
		ptmt.setInt(3, g.getAge());
		ptmt.setDate(4, new Date(g.getBirthday().getTime()));
		ptmt.setString(5, g.getEmail());
		ptmt.setString(6, g.getMobile());
		ptmt.setString(7, g.getUpdate_user());
		ptmt.setInt(8, g.getIsdel());
		ptmt.setInt(9, g.getId());
		ptmt.execute();
	}
	
	public void delGoddess(Integer id) throws SQLException{
		Connection conn = DBUtil.getConnection();
		String sql="" +
				" delete from  imooc_goddess " +
				" where id=? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setInt(1,id);
		ptmt.execute();
	}
	
	public List<Goddess> query() throws SQLException{
		Connection conn = DBUtil.getConnection();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("select user_name,age from imooc_goddess");
		List<Goddess> gs = new ArrayList<Goddess>();
		Goddess g = null;
		while(rs.next()){
			g = new Goddess();
			g.setUser_name(rs.getString("user_name"));
			g.setAge(rs.getInt("age"));
			gs.add(g);
		}
		return gs;
	}
	
	public List<Goddess> query(String name,String mobile,String email) throws SQLException{
		List<Goddess> result = new ArrayList<Goddess>();
		Connection conn = DBUtil.getConnection();
		StringBuilder sb=new StringBuilder();
		sb.append("select * from imooc_goddess  ");
		sb.append(" where user_name like ? and mobile like ? and email like ?");
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());
		ptmt.setString(1,"%"+name+"%");
		ptmt.setString(2,"%"+mobile+"%");
		ptmt.setString(3,"%"+email+"%");
		ResultSet rs = ptmt.executeQuery();
		Goddess g=null;
		while(rs.next()){
			g=new Goddess();
			g.setId(rs.getInt("id"));
			g.setUser_name(rs.getString("user_name"));
			g.setAge(rs.getInt("age"));
			g.setSex(rs.getInt("sex"));
			g.setBirthday(rs.getDate("birthday"));
			g.setEmail(rs.getString("email"));
			g.setMobile(rs.getString("mobile"));
			g.setCreate_date(rs.getDate("create_date"));
			g.setCreate_user(rs.getString("create_user"));
			g.setUpdate_date(rs.getDate("update_date"));
			g.setUpdate_user(rs.getString("update_user"));
			g.setIsdel(rs.getInt("isdel"));
			result.add(g);
		}
		return result;
	}
	
	
	public List<Goddess> query(List<Map<String,Object>> params) throws SQLException{
		List<Goddess> result = new ArrayList<Goddess>();
		Connection conn = DBUtil.getConnection();
		StringBuilder sb=new StringBuilder();
		sb.append("select * from imooc_goddess where 1=1 ");
		if(params!=null&¶ms.size()>0){
			for(int i=0;i<params.size();i++){
				Map<String,Object> map = params.get(i);
				sb.append(" and "+map.get("name")+map.get("rela")+map.get("value"));
			}
		}
		System.out.println(sb.toString());
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());
		
		ResultSet rs = ptmt.executeQuery();
		Goddess g=null;
		while(rs.next()){
			g=new Goddess();
			g.setId(rs.getInt("id"));
			g.setUser_name(rs.getString("user_name"));
			g.setAge(rs.getInt("age"));
			g.setSex(rs.getInt("sex"));
			g.setBirthday(rs.getDate("birthday"));
			g.setEmail(rs.getString("email"));
			g.setMobile(rs.getString("mobile"));
			g.setCreate_date(rs.getDate("create_date"));
			g.setCreate_user(rs.getString("create_user"));
			g.setUpdate_date(rs.getDate("update_date"));
			g.setUpdate_user(rs.getString("update_user"));
			g.setIsdel(rs.getInt("isdel"));
			result.add(g);
		}
		return result;
	}
	
	public Goddess get(Integer id) throws SQLException{
		Goddess g = null;
		Connection conn = DBUtil.getConnection();
		String sql="" +
				" select *  from  imooc_goddess " +
				" where id=? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setInt(1,id);
		ResultSet rs = ptmt.executeQuery();
		while(rs.next()){
			g = new Goddess();
			g.setId(rs.getInt("id"));
			g.setUser_name(rs.getString("user_name"));
			g.setAge(rs.getInt("age"));
			g.setSex(rs.getInt("sex"));
			g.setBirthday(rs.getDate("birthday"));
			g.setEmail(rs.getString("email"));
			g.setMobile(rs.getString("mobile"));
			g.setCreate_date(rs.getDate("create_date"));
			g.setCreate_user(rs.getString("create_user"));
			g.setUpdate_date(rs.getDate("update_date"));
			g.setUpdate_user(rs.getString("update_user"));
			g.setIsdel(rs.getInt("isdel"));
		}
		return g;
	}
	
	
}