JdbcDemo
一个jdbc的实例,
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// TODO Auto-generated method stub
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.用户信息
String url= "jdbc:mysql://localhost:3306/jdbctest?useUnicode=true&charaterEncoding=utf8&SSL=true";
String username= "root";
String password="";
//3.连接成功,创建数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SQL的对象去执行SQL,可能存在结果,查看返回结果
Statement statement = connection.createStatement();
String sql = "select * from users";
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集
while(resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
1.加载驱动
2.连接数据库DriverManager
3.获得执行sql的对象,Statement
4.获得返回的结果集
5.释放连接
注意:导入jdbc架包位置在所创建的java项目的lib目录下。
Statement执行sql的对象,PrePareStatement执行sql的对象
statement.executeQuery();//查询操作返回ResultSet
statement.execute();//执行任何SQL
statement.executeUpdate();//更新、插入、删除、都用这个,返回一个受影响的行数
ResultSet查询的结果集,封装了所有的查询结果
resultSet.getObject();//在不知道列类型的情况下使用
//如果指定列的类型就是用指定的类型。
resultSet.getInt();
resultSet.getDate();
遍历
resultSet.beforeFirst();//移动到最前面
resultSet.next();//移动到下一个
resultSet.privious();//移动到前一行
resultSet.absolute(row);//移动到指定行
------------------------------------------------------正文部分-------------------------------------------------------------------------------
jdbcDemo通过工具类实现
1.jdbcUtils工具类如下:
package test1.day4;
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 jdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static{try{
InputStream in = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty(driver);
url=properties.getProperty(url);
username = properties.getProperty(username);
password=properties.getProperty(password);
//驱动只加载一次
Class.forName(driver);
}catch(Exception e){
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(url, username, password);
}
//释放连接
public static void release(Connection conn,Statement sta,ResultSet res){
if(res!=null){
try {
res.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(sta!=null){
try {
sta.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
2.测试插入用例
package test1.day4;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection connection=null;
Statement sta=null;
ResultSet res = null;
try{
connection = jdbcUtils.getConnection();
sta=connection.createStatement();
String sql = "INSERT into student(`StudentNo`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`Email`)VALUES(5,'dy5',1,3,'123454345','打发','1324@qq.com');";
int i = sta.executeUpdate(sql);
if(i>0){
System.out.println("插入成功!");
}
}catch(Exception e){
e.printStackTrace();
}finally{
jdbcUtils.release(connection, sta, res);
}
}
}
3.db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&useSSL=true&characterEncoding=utf8
username=root
password=
4.测试所用表
CREATE TABLE `student` (
`StudentNo` int(4) NOT NULL,
`StudentName` varchar(20) DEFAULT NULL,
`Sex` tinyint(1) DEFAULT NULL,
`GradeId` int(11) DEFAULT NULL,
`Phone` varchar(11) DEFAULT NULL,
`Address` varchar(255) DEFAULT NULL,
`Email` varchar(50) DEFAULT NULL,
PRIMARY KEY (`StudentNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
5.测试成功,删除和修改同理。