1.新建info.properties 文件
driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://[127.0.0.1]:[1433/1434];databaseName=[dbName]
name=name
pwd=pwd
2.在同位置下创建DBHelper.java文件
package com.wyman.util;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class DBHelper {
private static String driver;
private static String url;
private static String name;
private static String pwd;
private static Connection con;
//初始化,获取四个变量的值
private static void init(){
Properties p=new Properties();
try {
p.load(DBHelper.class.getResourceAsStream("info.properties"));
driver=p.getProperty("driver");
url=p.getProperty("url");
name=p.getProperty("name");
pwd=p.getProperty("pwd");
} catch (IOException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getCon(){
init();
try {
Class.forName(driver);
try {
con=DriverManager.getConnection(url,name,pwd);
} catch (SQLException e) {
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return con;
}
}
3.如何做insert操作
package com.wyman.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class ITest {
public static void main(String[] args) throws SQLException {
Connection con=null;
PreparedStatement ps=null;
String sql="insert into [table_name] values (?,?,?)";
String sql2="insert into [table_name] ([列1],[列2],[列3]) values (?,?,?)";
con=DBHelper.getCon();
try {
ps=con.prepareStatement(sql2);
int i=ps.executeUpdate();
//i的值为1,则插入成功,为0,则失败
//返回值i为受影响行数
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(ps!=null){
ps.close();
}
if(con!=null){
con.close();
}
}
}
}
4.删除操作delete
package com.wyman.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DTest {
public static void main(String[] args) throws SQLException {
Connection con=null;
PreparedStatement ps=null;
String sql="delete [table_name] where [条件1] and [条件2] ...";
con=DBHelper.getCon();
try {
ps=con.prepareStatement(sql);
int i=ps.executeUpdate();
//i的值为1,则插入成功,为0,则失败
//返回值i为受影响行数
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(ps!=null){
ps.close();
}
if(con!=null){
con.close();
}
}
}
}
5.修改操作update
String sql="update [table_name] set [列名]=[值],[列名2]=[值] where [条件1] .....";
其余操作与删除,添加相同6,查询操作select
package com.wyman.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class STest {
public static void main(String[] args) throws SQLException {
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
String sql="select * from [table_name] where [条件1] and [条件2] ...";
String sql2="select [列名1],[列名2] from [table_name] where [条件1] and [条件2] ...";
con=DBHelper.getCon();
try {
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()){
//在次解析每一行数据
//1,rs.getString(列序列号)/rs.getInt(列序列号)
//2,rs.getString("列名")/rs.getInt("列名")
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(con!=null){
con.close();
}
}
}
}
6.手工提交事务
①,在获取ps之前,con.setAutoCommit(false); 默认值为true,意思是手动提交事务,不允许系统自动提交
②,在做完ps的executeUpdate()操作之后,con.commit(); 提交事务