Java with SQLite embedded database
Vložit
- čas přidán 20. 01. 2016
- This video is brought to you by SoftwarePulse.co.uk
If your java desktop application needs to store data in a database then a good solution is to embed a database into your solution. This approach avoids the need for the user to install a database separately from the application. This video demonstrates an application which installs the database as part of the application, builds the database tables and populates it with initial data.
One of the viewers, @thatnolan, pointed out that the code provided in the video at 24:00 mins has a problem with newer versions of SQLite and he kindly provided a workaround. As an alternative, there is also the option of changing the prepared statement to "insert into user(fname, lname) values(?,?);" and then calling "prep.setString(1, "John"); prep.setString(2, "McNeil"); prep.execute();". Note the change in the index number when calling the setString method. Hope these two options help.
Thank you!
for anyone getting the java.sql.SQLException: Values not bound to statement Exception, its because you didnt put in 3 values. At 24:00 mins he only puts in two values, You need to add prep.setInt(1, any int) to fill in the value. I think this is a thing with the newer SQLite which is why he didn't have the problem in the video.
Hi thatnolan, thanks for pointing out the issue with later versions of SQLite and for taking the time to provide a workaround. I will also post a comment for another solution to give people options. Once again, thanks for your comment.
Worked perfectly for me. Thank you
Hi Wess, good to hear it worked for you and thanks for taking the time to let me know. I appreciate it.
Regards,
John
it was helpful for me. thank you..
awesome tutorial c:
Great Video and thanks for the video ^^
Hi Abdo,
Glad you liked the video. I understand your time is precious so thank you for taking the time to comment.
Regards,
John
Very helpful video. Play in 1.5 speed
Great tutorial, keep going
thank you so much , very useful , appreciated !!
You are very welcome. Glad you enjoyed it.
Thank you man!
Helped with my final project :3
Thanks John.
You're welcome.
Thank you so much. :)
You're welcome, glad you enjoyed it.
This video is very good to understand the global setup but if you are watching this after 2018-19 please be careful has some methods are not appropriate, certain exceptions need to be corrected and you will have to read the documentation to write secure and effective code because certain things shown here are pretty bad practices
How would you use the addUser() function within the program?
BTW, exists fine Valentina SQLite Server if you suddenly need SQLite for multi-user. Also they have cool Valentina Studio. Great to manage SQLite and other dbs.
Great Video good job
Hi Rojae, Glad you liked the video.
Regards,
John
This was really useful, thank you for sharing!
Stefan, Thanks for the feedback. Glad you enjoyed it.
Hi , i have problem,how can i protect this sqlitedb file from being deletion?if i deploy java project in locale
Thank you very much!
You're welcome Jonas!
Thanks a lot Sir
Yahaya, Your most welcome, Cheers John
when i was changing instead of john it is not showing can any one help for that
could you import a sql database form localhost xamp to sqlite so that it is only the app with no xamp server ,i mean import the sql to sqlite , how is it done
I'm getting errors saying the database doesn't exist. I check the workspace, and it does create SQLiteTest.db1, but I can't get anything in the terminal.
Here's what I'm getting in the terminal:
java.sql.SQLException: path to '/home/leo/work/mydatabase.db': '/home/leo' does not exist
Hi Justin, it looks like the code is trying to open the database from the subdirectory "/home/leo" which does not exist. This directory should exist under the project directory. So the directory path would be something like "../workspace/projectname/home/leo/". Not sure why you are seeing this if the code ran and created the database as it uses the same path to created the database as it does to access it on subsequent runs. You do need to watch out if creating the database in another location as you can run into access errors.
Cheers
John
Hey. Shouldn't CREATE TABLE IF NOT EXIST do the job instead of checking with the sqlite_master?
Yes, you could do that if you prefer that approach and are happy to handle pre-populating the table with data when you create it. The great thing about software development is that there is more than one solution to a problem. Finding a solution that works for you is what it is all about. Glad to see you take the code here and modify it to suit your personal approach.
how can i unsubscribe the spam mail?
Can i import csv file filled with data instead of declaring the data manually in the program?
Yes, you can import data from a CSV file. You can either get a SQLite tool (I use DB Browser) for editing SQLite files and use that to import the CSV file or you can write a Java application to process the CSV file and write the contents into the database.
Hope this helps,
John.
You should cut the spaces, those 1-2 seconds where you don`t say anything. Great tutorial by the way.
Thanks for the feedback Dani, with each video I try to improve things. Audio is something that needs work but glad you like the information.
Of course. I searched for something that you presented here in order to improve an older softvare, but without disturbing the server owner for setting up a database. Your video helped a lot. Thanks for your answer.
Quick question: is that windows xp or are you using some sort of theme
Hi Gameloser 1, I must confess that I do not know what operating system was used here. It may well have been XP but just as likely a more recent version of Windows. I use different physical as well as virtual machines for my videos and they use various operating systems. Why do you ask?
Regards,
John
@@SoftwarepulseCoUk No reason! I was just curious .
I love you.
Sarah, I can only assume you found what you were looking for. I'm glad I was able to help. Regards John
sir,,,,,,in your case the database size is 2KB but I need to increase the size ,...how can I do tht?????
If you want to increase the size of the database then you could add more data. I only add three rows to my single table. You could add more rows as well as add more tables. This will increase the size of the database.
one thing i don’t understand is how the db knows to auto generate the ID values.
Hi Annalise, thanks for asking this question. If you look at the initialise() method @22:42 in the video, you will see we are creating the database table. Part of the SQL to create the table specifies that 'id' is the primary key. A primary key is always unique and allows the user to access a specific row of data using that key. As such, because we have id as the primary key SQLite knows to increment the value each time a new row is added to the table. Therefore we do not need to specify a value for this when adding a row. Hope that helps explain why the db auto generates the ID values.
Regards,
John
My question is.
Is there some way to read from a database in a separate folder by giving its location on my computer.
Or does it have to be in the same file location.
I have a database i want to be able to read and modify but that database needs to be in a separate location as it works with a different application
Hi Zalied, If I correctly understand your question you are trying to access an existing database located on your computer. You can do this by changing the line of code "con = DriverManager.getConnection("jdbc:sqlite:data/SQLiteTest1.db");" where "data/SQLiteTest1.db" is changed to point to you database say "c:/code/dbs/another.db". Hope this helps.
Cheers
thank you
If possible you can send me the explanation in the video and thanks in advance
Hi Yahia, I you mean the source code for this tutorial it can be downloaded from the softwarepulse.co.uk web site. You will need to provide an email address to download. There is also a blog entry which covers this material if you prefer the read rather than listen. Again go to the web site and then click on the blog link to take you to the blog site.
I followed your tutorial and got into serious problems when trying to use more than 1 query in the same function, like "SELECT" then " INSERT". You should remake this tutorial teaching how to close the connections properly.
Hi Henrique, sorry to hear you ran into problems. I must confess I was more focused on showing how you can embed a database into an application than handling multiple SQL statements or even closing down connections. I hope you were able to find a video that could help.
John McNeil Hey John. Thanks for your response. Yeah, the videos is more focused on working with SQLite. But no problem, handling multiple connections weren't so hard. Thanks!
hi, my main problem is that whenever I make the url for the db to be created; it can't be created and i get nothing so, any ideas? regards
Hi Alisalman, I am assuming that when you say "make the url for the db" you are referring to the line
"con = DriverManager.getConnection("jdbc:sqlite:SQLiteTest1.db");". It may be that you do not have permission to create the database in that location. Try creating the database in a location that you know you have permissions. You can change the location by specifying a path like this "jdbc:sqlite:C:\\Users\\jmcneil\\Documents\\Java\\SQLiteTest1.db".
Hope that helps.
Regards,
John
@@SoftwarepulseCoUk exactly that's what I meant, I'll try it now but thank you anyway especially for your continuous quick response
@@SoftwarepulseCoUk unfortunately it's the same, can I send you my code to check the problem? cause honestly am confused
@@SoftwarepulseCoUk this is a simple code just to create a DB that can't be created, would you please at least just point out where is the issue or gap?
import java.sql.*;
public class DBtest {
public static void main(String[] args) {
Connection cn=null;
try {
Class.forName("org.sqlite.JDBC"); //loading JDBC driver into eclipse's driver manager
cn=DriverManager.getConnection("jdbc:sqlite:D:\\Eclipse codes\\alex.db");
System.out.println("open DB");
}
catch (Exception e){
System.out.println("the DB wasn't created");
}
}
}
Hi Alisalman, I used the code you provided and got the same error you are seeing. However, I do not have a D drive so that is not unexpected. I added this line of code "System.out.println(e.getMessage());" to the exception part and got this message "path to 'D:\Eclipse codes\alex.db': 'D:\' does not exist". What do you get? Also, avoid using paths that contain spaces for now as that can cause problems.
Let me know how you get on.
Regards,
John
I'm not sure why, but when I try to use CREATE TABLE (name) it gives me an unrecognized statement error
Hi, from the information provided I'm assuming that you are calling something like state2.execute("CREATE TABLE xxxxx.... The table name is not in parenthesis which might be where the problem is.
can i see the source code???
Hi Vivien, you can download the source code for the Java with SQLite embedded database from www.softwarepulse.co.uk. Go to the courses section and pick this tutorial. An email address is required.
Any body having Bound errors try "INSERT INTO user(fname,lname) VALUES(?,?)"
Me
@@vanessarenteriavidal9225 lol i solved it. cant even remember what i did. but i think its from the prepared statement and execute query
Can I have access to the code?
Hi, you can download the source code for this example from the web site at softwarepulse.co.uk/tutorials/java-with-sqlite-embedded-database/. You will need to provide en email address but I only use this to let people know about new material and you can unsubscribe as soon as you have downloaded the material if you want.
Let me know if there is anything else and I hope you find the source code helpful.
could not do it
could not download
Try again. The page showing the files had broken links. Now all sorted. Thanks for pointing this out.
@@SoftwarepulseCoUk The link is broken... :/
thanks man ;) please provide us the code please :D
Hi Mohammed, you can download the source code for the Java with SQLite embedded database from www.softwarepulse.co.uk. Go to the courses section and pick this tutorial. An email address is required.
I think in order to understand this for beginners you have a little knowledge about basic SQL language to understand how the codes work.
Hi Lexter, I think that is fair to say that a little SQL knowledge is required to understand how to add and read data from the database and I do not explain this in the video. A quick web search of the SQL used should provide an explanation. If anyone has a specific question about a command I'm happy to try and answer it.
Almost no explanation. Much talking, no info for a foreigner :(.