Spring jdbcTemplate连接SQL Server 2019

1. 导入所需jar包
   
   官网下载SQL Server JDBC 驱动jar包
   https://docs.microsoft.com/zh-cn/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15
2. src目录下创建属性文件jdbc.properties

注意填写自己的数据库名，登录名和密码

jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=jdbc:sqlserver://127.0.0.1:1433;DatabaseName=***
jdbc.username=***
jdbc.password=***

3. src目录下创建config包，在该包中创建配置类

package config;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@Configuration 
@ComponentScan(basePackages = {"dao","service"})
@PropertySource(value={"classpath:jdbc.properties"},ignoreResourceNotFound=true) 
@EnableTransactionManagement
// value={"classpath:jdbc.properties","xx","xxx"}
public class SpringJDBCConfig {
	@Value("${jdbc.url}")//jdbc.url
    private String jdbcUrl;
    @Value("${jdbc.driverClassName}")
    private String jdbcDriverClassName;
    @Value("${jdbc.username}")
    private String jdbcUsername;
    @Value("${jdbc.password}")
    private String jdbcPassword;

    @Bean
    public DriverManagerDataSource dataSource() {
    	DriverManagerDataSource myDataSource  = new DriverManagerDataSource();
        
    	myDataSource.setDriverClassName(jdbcDriverClassName);;
       
    	myDataSource.setUrl(jdbcUrl);
       
    	myDataSource.setUsername(jdbcUsername);
        
    	myDataSource.setPassword(jdbcPassword);
        return myDataSource;
    }

    @Bean(value="jdbcTemplate")
    public JdbcTemplate getJdbcTemplate() {
    	return new JdbcTemplate(dataSource());
    }

    @Bean
    public DataSourceTransactionManager transactionManager() {
    	DataSourceTransactionManager dt = new DataSourceTransactionManager();
    	dt.setDataSource(dataSource());
    	return dt;
    }
}

4. sql server中建表

create table xxx
(
uid int primary key,
uname varchar(20),
usex varchar(10)
)

5. 在src目录下创建包entity，在该包中创建实体类MyUser

package entity;
public class MyUser {
	private Integer uid;
	private String uname;
	private String usex;
	public Integer getUid() {
		return uid;
	}
	public void setUid(Integer uid) {
		this.uid = uid;
	}
	public String getUname() {
		return uname;
	}
	public void setUname(String uname) {
		this.uname = uname;
	}
	public String getUsex() {
		return usex;
	}
	public void setUsex(String usex) {
		this.usex = usex;
	}
	public String toString() {
		return "[uid=" + uid +", uname=" + uname + ", usex=" + usex + "]";
	}
}

6. 在src目录下创建dao包，在该包中创建数据访问接口TestDao和接口实现类TestDaoImpl （数据访问层）

//TestDao
package dao;
import java.util.List;
import entity.MyUser;
public interface TestDao {
	public int update(String sql, Object[] param);
	public List<MyUser> query(String sql, Object[] param);
}

//TestDaoImpl
package dao;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import entity.MyUser;
@Repository
public class TestDaoImpl implements TestDao{
	 @Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public int update(String sql, Object[] param) {
		return jdbcTemplate.update(sql, param);  
	}

	@Override
	public List<MyUser> query(String sql, Object[] param) {
		RowMapper<MyUser> rowMapper = new BeanPropertyRowMapper<MyUser>(MyUser.class);
		return jdbcTemplate.query(sql, rowMapper);
	}
}

7. 在src目录下，创建service包，在该包中创建了数据访问接口TestService和接口实现类TestServiceImpl （业务逻辑层）

//TestService
package service;
public interface TestService {
	public void testJDBC();
}

//TestServiceImpl
package service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import dao.TestDao;
import entity.MyUser;
@Service
@Transactional
public class TestServiceImpl implements TestService{
	@Autowired
	public TestDao testDao;
	@Override
	public void testJDBC() {
	    //插入数据
		String insertSql = "insert into My_user values(?,?,?)";
		Object param1[] = {1,"chenheng5", "女"};
		Object param2[] = {2,"chenheng6", "女"};
		testDao.update(insertSql, param1);
		testDao.update(insertSql, param2);
		//查询
		String selectSql ="select * from xxx";
		List<MyUser> list = testDao.query(selectSql, null);
		for(MyUser mu : list) {
			System.out.println(mu);
		}
	}
}

8. 在config包中创建测试类TestJDBC

package config;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import service.TestService;
public class TestJDBC {
	public static void main(String[] args) {
		AnnotationConfigApplicationContext appCon = 
			new AnnotationConfigApplicationContext(SpringJDBCConfig.class);
		TestService ts = appCon.getBean(TestService.class);
		ts.testJDBC();
		appCon.close();
	}
}

运行结果如下：

若有侵权烦请联系作者删除