前提说明
前提:java可成功连接数据库
SQL Srver
数据库: Text
表:t_student
表格内容:
Eclipse
程序结构:
说明:MainText.java为在主函数测试所用,MyText.java为用Junit直接测试方法所用,两种方式皆可
程序实现
JDBCUtil.java
①直接方式
package MyJDBCUtil;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtil {
static String url = null;
static String user = null;
static String password = null;
/**
* 获取连接对象
*/
public static Connection getConn() {
Connection connection = null;
java.sql.DriverManager.registerDriver(new SQLServerDriver());
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
url = "jdbc:sqlserver://localhost:1433;DatabaseName=text01";
user = "sa";
password = "172228";
//参数一:协议+访问的数据库;参数二:用户名;参数三:密码
connection = DriverManager.getConnection(url,user,password);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
/**
* 释放资源
*/
public static void release(Connection connection ,Statement st, ResultSet rs) {
closeRs(rs);
closeSt(st);
closeConn(connection);
}
private static void closeRs(ResultSet rs) {
try {
if(rs != null) {
rs.close();
}
rs = null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private static void closeSt(Statement st) {
try {
if(st != null) {
st.close();
}
st = null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private static void closeConn(Connection connection) {
try {
if(connection != null) {
connection.close();
}
connection = null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
②配置文件方式
注意:这里配置文件(jdbc.properties)是放在src下的
package MyJDBCUtil;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtil {
static String driverClass = null;
static String url = null;
static String user = null;
static String password = null;
static {
try {
//创建一个属性配置文件
Properties properties = new Properties();
//InputStream is = new FileInputStream("jdbc.properties");
InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");//使用类加载器去读取src底下的资源文件
//导入输入流
properties.load(is);
//读取属性
driverClass = properties.getProperty("driverClass");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 获取连接对象
*/
public static Connection getConn() {
Connection connection = null;
java.sql.DriverManager.registerDriver(new SQLServerDriver());
try {
Class.forName(driverClass);
//参数一:协议+访问的数据库;参数二:用户名;参数三:密码
connection = DriverManager.getConnection(url,user,password);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
/**
* 释放资源
*/
public static void release(Connection connection ,Statement st, ResultSet rs) {
closeRs(rs);
closeSt(st);
closeConn(connection);
}
private static void closeRs(ResultSet rs) {
try {
if(rs != null) {
rs.close();
}
rs = null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private static void closeSt(Statement st) {
try {
if(st != null) {
st.close();
}
st = null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private static void closeConn(Connection connection) {
try {
if(connection != null) {
connection.close();
}
connection = null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
jdbc.properties
driverClass = com.microsoft.sqlserver.jdbc.SQLServerDriver
url = jdbc:sqlserver://localhost:1433;DatabaseName=text01
user = sa
password = 172228
TextMain.java / MyText.java(里面的方法)
查找数据
SQL语句:
SELECT *
FROM t_student ;
实现方法:
public void textSelect() {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
//查询
try {
//获取连接对象
conn = JDBCUtil.getConn();
//根据连接对象,得到statement
st = conn.createStatement();
//执行SQL语句,返回ResultSet
String sql = "select * from t_student";
rs =st.executeQuery(sql);
//便来结果集
while(rs.next()) {
String name = rs.getString("name");
int age = rs.getInt("age");
System.out.println(name+" "+age);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtil.release(conn, st, rs);
}
}
插入数据
SQL语句:
INSERT *
INTO t_student
VALUES(007,'papa','20') ;
实现方法:
public void textInsert() {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
//查询
try {
//获取连接对象
conn = JDBCUtil.getConn();
//根据连接对象,得到statement
st = conn.createStatement();
//执行添加
String sql = "insert into t_student values(007,'papa','20')";
int result = st.executeUpdate(sql);
if(result > 0) {
System.out.println("添加成功");
}
else {
System.out.println("添加失败");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
删除数据
SQL语句:
DELETE *
FROM t_student
WHERE name ='papa' ;
实现方法:
public void textDelete() {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
//查询
try {
//获取连接对象
conn = JDBCUtil.getConn();
//根据连接对象,得到statement
st = conn.createStatement();
//执行添加
String sql = "delete from t_student where name ='papa'";//满足条件的全部删除
int result = st.executeUpdate(sql);
if(result > 0) {
System.out.println("删除成功");
}
else {
System.out.println("删除失败");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
修改数据
SQL语句:
UPDATE t_student
SET age = 21
WHERE name ='bubu' ;
实现方法:
public void textUpdate() {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
//查询
try {
//获取连接对象
conn = JDBCUtil.getConn();
//根据连接对象,得到statement
st = conn.createStatement();
//执行添加
String sql = "update t_student set age = 21 where name ='papa'";//满足条件的全部修改
int result = st.executeUpdate(sql);
if(result > 0) {
System.out.println("修改成功");
}
else {
System.out.println("修改失败");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
结果
查询方法执行结果:
插入方法执行结果:
删除方法执行结果:
修改方法执行结果:
警告出现的原因我还没有搞清楚,先写在这里,之后弄清楚了再修改。