sqlite安装与学习

来源:互联网 发布:开淘宝店货源从哪来 编辑:程序博客网 时间:2024/03/29 22:09


图形化管理工具:sqlite developer



创建数据库:sqlite3 test.db


创建表格:
create table employee(
id integer primary key autoincrement,
name varchar(20),
pwd varchar(20),
age integer
);


插入数据:
insert into employee(name,pwd,age) values('zhaodeng','1234','25');
insert into employee(name,pwd,age) values('Tom','1234','30');


查询显示数据
select * from employee;


cmd操作展示:

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
C:\Users\ZhaoD>d:

D:\>cd D:\Jdk-eclipsejee-tomcat-ant\sqlite

D:\Jdk-eclipsejee-tomcat-ant\sqlite>sqlite3 test.db
SQLite version 3.11.1 2016-03-03 16:17:53
Enter ".help" for usage hints.
sqlite> create table employee(
   ...> id integer primary key autoincrement,
   ...> name varchar(20),
   ...> pwd varchar(20),
   ...> age integer
   ...> );
sqlite> insert into employee(name,pwd,age) values('zhaodeng','1234','25');
sqlite> insert into employee(name,pwd,age) values('Tom','1234','30');
sqlite> select * from employee;
1|zhaodeng|1234|25
2|Tom|1234|30

sqlite> 



------------------------------------------------------------------------------------

JDBC连接sqlite数据库程序


package bo;import java.sql.Statement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;public class DbTEst {public static void main(String[] args) {// TODO Auto-generated method stubList<Employee> employees = fetchData();for (Employee e : employees) {System.out.println(e.toString());}Employee employee = login("Tom", "1234");if(employee!=null){System.out.println("登录成功,欢迎"+employee.getName());System.out.println(employee.toString());}else{System.out.println("登录失败");}}private static Employee login(String name, String pwd) {Employee employee = null;Connection connection = null;try {Class.forName("org.sqlite.JDBC");connection = DriverManager.getConnection("jdbc:sqlite:D:/Jdk-eclipsejee-tomcat-ant/sqlite/test.db");String string = "select * from employee where name =? and pwd =?";PreparedStatement statement = (PreparedStatement) connection.prepareStatement(string);statement.setString(1, name);statement.setString(2, pwd);ResultSet resultSet = statement.executeQuery();if (resultSet.next()) {employee = new Employee(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3),resultSet.getInt(4));}} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}finally {if (connection != null) {try {connection.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}return employee;}private static List<Employee> fetchData() {List<Employee> employees = new ArrayList<Employee>();Employee employee = null;Connection connection = null;try {Class.forName("org.sqlite.JDBC");connection = DriverManager.getConnection("jdbc:sqlite:D:/Jdk-eclipsejee-tomcat-ant/sqlite/test.db");Statement statement = (Statement) connection.createStatement();String string = "select * from employee";ResultSet rSet = statement.executeQuery(string);while (rSet.next()) {employee = new Employee(rSet.getInt(1), rSet.getString(2), rSet.getString(3), rSet.getInt(4));employees.add(employee);}} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {// TODO: handle exceptione.printStackTrace();} finally {if (connection != null) {try {connection.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}return employees;}}


0 0