简单的JDBC连接,没有用c3p0之类的连接方式,所有连接信息也直接放在了工具类里,有需要的自己去改
JDBCUtils1.java
package mysqlutils;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
public class JDBCUtils1 {
private static final String DBDRIVER = "com.mysql.jdbc.Driver";// 驱动类类名
private static final String DBNAME = "school";// 数据库名
private static final String DBURL = "jdbc:mysql://localhost:3306/" + DBNAME;// 连接URL
private static final String DBUSER = "root";// 数据库用户名
private static final String DBPASSWORD = "tjw";// 数据库密码
private static Connection conn = null;
private static PreparedStatement ps = null;
private static ResultSet rs = null;
/*
* 获取数据库连接
*/
public static Connection getConnection() {
try {
Class.forName(DBDRIVER);// 注册驱动
conn = (Connection) DriverManager.getConnection(DBURL, DBUSER,
DBPASSWORD);// 获得连接对象
System.out.println("成功加载MYSQL驱动程序");
} catch (ClassNotFoundException e) {// 捕获驱动类无法找到异常
System.out.println("找不到MYSQL驱动程序");
System.out.println(e.toString());
e.printStackTrace();
} catch (SQLException e) {// 捕获SQL异常
e.printStackTrace();
}
return conn;
}
public static ResultSet select(String sql) throws Exception {
try {
conn = getConnection();
ps = (PreparedStatement) conn.prepareStatement(sql);
rs = ps.executeQuery(sql);
return rs;
} catch (SQLException sqle) {
throw new SQLException("select data Exception: "
+ sqle.getMessage());
} catch (Exception e) {
throw new Exception("System error: " + e.getMessage());
}
}
/*
* 增删改均调用这个方法
*/
public static void updata(String sql) throws Exception {
try {
conn = getConnection();
ps = (PreparedStatement) conn.prepareStatement(sql);
ps.executeUpdate();
} catch (SQLException sqle) {
throw new SQLException("insert data Exception: "
+ sqle.getMessage());
} finally {
try {
if (ps != null) {
ps.close();
}
} catch (Exception e) {
throw new Exception("ps close exception: " + e.getMessage());
}
try {
if (conn != null) {
conn.close();
}
} catch (Exception e) {
throw new Exception("conn close exception: " + e.getMessage());
}
}
}
}
JDBCUtils2.java
package sqlserverutils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCUtils2 {
private static final String DBDRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";// 驱动类类名
private static final String DBNAME = "school";// 数据库名
// jdbc:sqlserver://localhost:1433;DatabaseName=school","sa","tjw"
private static final String DBURL = "jdbc:sqlserver://localhost:1433;DatabaseName="+DBNAME;// 连接URL
private static final String DBUSER = "sa";// 数据库用户名
private static final String DBPASSWORD = "tjw";// 数据库密码
private static Connection conn = null;
private static PreparedStatement ps = null;
private static ResultSet rs = null;
/*
* 获取数据库连接
*/
public static Connection getConnection() {
try {
Class.forName(DBDRIVER);// 注册驱动
conn = DriverManager.getConnection(DBURL,DBUSER,
DBPASSWORD);// 获得连接对象
System.out.println("成功加载SQL Server驱动程序");
} catch (ClassNotFoundException e) {// 捕获驱动类无法找到异常
System.out.println("找不到SQL Server驱动程序");
System.out.println(e.toString());
e.printStackTrace();
} catch (SQLException e) {// 捕获SQL异常
e.printStackTrace();
}
return conn;
}
public static ResultSet select(String sql) throws Exception {
try {
conn = getConnection();
ps = (PreparedStatement) conn.prepareStatement(sql);
rs = ps.executeQuery();
return rs;
} catch (SQLException sqle) {
throw new SQLException("select data Exception: "
+ sqle.getMessage());
} catch (Exception e) {
throw new Exception("System error: " + e.getMessage());
}
}
/*
* 增删改均调用这个方法
*/
public static void updata(String sql) throws Exception {
try {
conn = getConnection();
ps = (PreparedStatement) conn.prepareStatement(sql);
ps.executeUpdate();
} catch (SQLException sqle) {
throw new SQLException("insert data Exception: "
+ sqle.getMessage());
} finally {
try {
if (ps != null) {
ps.close();
}
} catch (Exception e) {
throw new Exception("ps close exception: " + e.getMessage());
}
try {
if (conn != null) {
conn.close();
}
} catch (Exception e) {
throw new Exception("conn close exception: " + e.getMessage());
}
}
}
}
Student.java
package domain;
public class Student {
int sno;
String sname;
int sage;
String ssex;
public Student(int sno, String sname, int sage, String ssex) {
super();
this.sno = sno;
this.sname = sname;
this.sage = sage;
this.ssex = ssex;
}
public int getSno() {
return sno;
}
public void setSno(int sno) {
this.sno = sno;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public int getSage() {
return sage;
}
public void setSage(int sage) {
this.sage = sage;
}
public String getSsex() {
return ssex;
}
public void setSsex(String ssex) {
this.ssex = ssex;
}
@Override
public String toString() {
return "Student [sno=" + sno + ", sname=" + sname + ", sage=" + sage
+ ", ssex=" + ssex + "]";
}
}
Test.java
package MainClass;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import domain.Student;
import mysqlutils.JDBCUtils1;
/**
* 1.插入删除修改写法其实都是一样的,唯一的区别在于SQL语句不同,直接替换相应的SQL语句就行了,下边我分开写是为了看的更清楚
* 2.用之前只需要去JDBCUtils1中修改DBNAME、DBPASSWORD就好,如果不是root用户则顺带改了DBUSER
* 3.查询的调用方法:ResultSet rs = JDBCUtils1.select(sql);
* 4.增删改查调用的方法:JDBCUtils1.updata(sql);
* 5.注意把我libs包下的jar包导入并添加到path中
* 6.如果需要使用通配符,参考课本339页Example11_5.java,这里JDBCUtils1.getConnection()获取连接以后,自己往下写几行代码就出来了
* 有问题就try——catch捕获,这个不好封装
* @author Lenovo_PC
*
*/
public class Test {
public static void main(String[] args) {
// TODO Auto-generated method stub
// selectData();//查询数据
// insertData();//插入数据
// deleteData();//删除数据
// updateDate();//修改数据
}
/**
* 修改数据
*/
private static void updateDate() {
System.out.println("修改MySQL数据库数据");
String sql = "update student set sname = 'amy' where sname = 'tom'";
try {
JDBCUtils1.updata(sql);
System.out.println("修改成功!");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 删除数据
*/
private static void deleteData() {
System.out.println("删除MySQL数据库数据");
String sql = "delete from student where sname = 'tom'";
try {
JDBCUtils1.updata(sql);
System.out.println("删除成功!");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 插入数据
*/
private static void insertData() {
System.out.println("插入MySQL数据库数据");
//这里注意Sno,最好别重复,不过没设置主键,重复也没啥关系
String sql = "insert into student (sno, sname, sage, ssex) values (4, 'tom', 18, '女')";
try {
JDBCUtils1.updata(sql);
System.out.println("插入成功!");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 查询数据
*/
private static void selectData() {
System.out.println("查询MySQL数据库数据");
String sql = "select * from student";
List<Student> list = new ArrayList<Student>();
try {
ResultSet rs = JDBCUtils1.select(sql);
while (rs.next()) {
list.add(new Student(rs.getInt("sno"), rs.getString("sname"), rs.getInt("sage"), rs.getString("ssex")));
}
for (int i = 0; i < list.size(); i++) {
System.out.println(list.get(i));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Test2.java
package MainClass;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import mysqlutils.JDBCUtils1;
import sqlserverutils.JDBCUtils2;
import domain.Student;
/**
* 测试SQL Server的类,直接复制的Test
* 就是把JDBCUtils1改为JDBCUtils2
* @author Lenovo_PC
*
*/
public class Test2 {
public static void main(String[] args) {
// TODO Auto-generated method stub
// selectData();//查询数据
// insertData();//插入数据
// deleteData();//删除数据
// updateDate();//修改数据
}
/**
* 修改数据
*/
private static void updateDate() {
System.out.println("修改SQL Server数据库数据");
String sql = "update student set sname = 'amy' where sname = 'tom'";
try {
JDBCUtils2.updata(sql);
System.out.println("修改成功!");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 删除数据
*/
private static void deleteData() {
System.out.println("删除SQL Server数据库数据");
String sql = "delete from student where sname = 'tom'";
try {
JDBCUtils2.updata(sql);
System.out.println("删除成功!");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 插入数据
*/
private static void insertData() {
System.out.println("插入SQL Server数据库数据");
//这里注意Sno,最好别重复,不过没设置主键,重复也没啥关系
String sql = "insert into student (sno, sname, sage, ssex) values (4, 'tom', 18, '女')";
try {
JDBCUtils2.updata(sql);
System.out.println("插入成功!");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 查询数据
*/
private static void selectData() {
System.out.println("查询SQL Server数据库数据");
String sql = "select * from student";
List<Student> list = new ArrayList<Student>();
try {
ResultSet rs = JDBCUtils2.select(sql);
while (rs.next()) {
list.add(new Student(rs.getInt("sno"), rs.getString("sname"), rs.getInt("sage"), rs.getString("ssex")));
}
for (int i = 0; i < list.size(); i++) {
System.out.println(list.get(i));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
注意!!
SQL Server数据库连接的时候可能会报错,报错情况如下:
文字描述:
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: Connection refused: connect. Please verify the connection properties and check that a SQL Server instance is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.
解决的办法:TCP/IP协议给禁止了。点击“SQL Server Configuration Manager" -> "SQL Server XXX Network Configuration -> Protocols for MSSQL SERVER -> TCP/IP,如果是被禁止了,就启动一下
这儿的话最近碰上个操蛋的问题,就是开启了这个TCP/IP后还是报上边的错误,找了半天是端口的问题,你的电脑可能是用的动态端口,而不是指定的1433,解决的办法截图在下边了,自己照着做吧,打字太累了
还有就是更改完配置最好重启一下服务,两个办法,一是去任务管理器自己找到相应服务结束了,而是通过这个配制管理器
再说一下SQL Server附加数据库的问题
在sql 2008 R2附加数据库的时候出现如下的错误:无法打开物理文件 “H:\LittleRan\SQL\SHOOL\Example.mdf”。操作系统错误 5:“5(拒绝访问。)”。 (Microsoft SQL Server,错误: 5120)
解决办法就是添加Everyone用户,然后给它权限,数据库和日志文件都要改,这里不截图了,给个相关链接自己跳转过去看吧
https://jingyan.baidu.com/article/c275f6ba2f7d41e33d75678d.html
其他也没什么好解释的了
源码及测试数据库
下边在提供一个不错的工具类,与上个相比的话使用prepareStatement可以防止SQL注入,并且可以携带参数,另外数据库链接部分的信息是通过外部.properties文件获取的,后期修改只针对.properties文件即可
JdbcUtil类:
package com.imooc.page.util;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
public class JDBCUtil {
private static String USERNAME;
private static String PASSWORD;
private static String DRIVER;
private static String URL;
private Connection connection;
private PreparedStatement pstmt;
private ResultSet resultSet;
static{
loadConfig();
}
/**
* 加载数据库配置信息,并给相关的属性赋值
*/
public static void loadConfig(){
try {
InputStream inStream = JDBCUtil.class
.getResourceAsStream("/jdbc.properties");
Properties prop = new Properties();
prop.load(inStream);
USERNAME = prop.getProperty("jdbc.username");
PASSWORD = prop.getProperty("jdbc.password");
DRIVER = prop.getProperty("jdbc.driver");
URL = prop.getProperty("jdbc.url");
} catch (Exception e) {
throw new RuntimeException("读取数据库配置文件异常!", e);
}
}
public JDBCUtil() {
}
public Connection getConnection() {
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (Exception e) {
throw new RuntimeException("get connection error!");
}
return connection;
}
/**
* 执行更新操作
* @param sql sql语句
* @param params 执行参数
* @return 执行结果
* @throws SQLException
*/
public boolean updateByPreparedStatement(String sql, List<?> params)
throws SQLException {
boolean flag = false;
int result = -1;// 表示当用户执行添加删除和修改的时候所影响数据库的行数
pstmt = connection.prepareStatement(sql);
int index = 1;
// 填充sql语句中的占位符
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
result = pstmt.executeUpdate();
flag = result > 0 ? true : false;
return flag;
}
/**
* 执行查询操作
* @param sql sql语句
* @param params 执行参数
* @return
* @throws SQLException
*/
public List<Map<String, Object>> findResult(String sql, List<?> params)
throws SQLException {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
int index = 1;
pstmt = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int cols_len = metaData.getColumnCount();
while (resultSet.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 0; i < cols_len; i++) {
String cols_name = metaData.getColumnName(i + 1);
Object cols_value = resultSet.getObject(cols_name);
if (cols_value == null) {
cols_value = "";
}
map.put(cols_name, cols_value);
}
list.add(map);
}
return list;
}
/**
* 释放资源
*/
public void releaseConn() {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
.properties文件:
jdbc.username = root
jdbc.password = **your password**
jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/dividepage